PostgreSQL Joins

---

In PostgreSQL, joins are used to combine rows from two or more tables based on a related column between them.

Joins are essential for retrieving data from multiple tables and creating meaningful result sets.

PostgreSQL supports several types of joins:

1. INNER JOIN:

   • Returns only the rows that have matching values in both tables.
   • Syntax:

     SELECT * FROM table1 INNER JOIN table2 ON table1.column = table2.column;

2. LEFT JOIN (or LEFT OUTER JOIN):

   • Returns all rows from the left table and the matching rows from the right table. If there's no match, NULL values are returned for columns from the right table.
   • Syntax:

     SELECT * FROM table1 LEFT JOIN table2 ON table1.column = table2.column;

3. RIGHT JOIN (or RIGHT OUTER JOIN):

   • Similar to LEFT JOIN but returns all rows from the right table and the matching rows from the left table.
   • Syntax:

     SELECT * FROM table1 RIGHT JOIN table2 ON table1.column = table2.column;

4. FULL JOIN (or FULL OUTER JOIN):

   • Returns all rows when there is a match in either the left or right table. If there's no match, NULL values are returned for columns from the table without a match.
   • Syntax:

     SELECT * FROM table1 FULL JOIN table2 ON table1.column = table2.column;

5. CROSS JOIN (or Cartesian Join):

   • Returns the Cartesian product of rows from both tables. It doesn't require an "ON" condition.
   • Syntax:

     SELECT * FROM table1 CROSS JOIN table2;

6. SELF JOIN:

   • A join in which a table is joined with itself. This is useful when dealing with hierarchical or recursive data structures.
   • Syntax:

     SELECT e1.name, e2.name FROM employees e1 INNER JOIN employees e2 ON e1.manager_id = e2.employee_id;

7. Non-Equi Join:

   • In addition to the usual equality condition, you can join tables using non-equality conditions, such as greater than or less than.
   • Syntax (greater than condition):

     SELECT * FROM table1 INNER JOIN table2 ON table1.column > table2.column;

8. Multiple Joins:

   • You can join multiple tables in a single query.
   • Syntax:

     SELECT * FROM table1 INNER JOIN table2 ON table1.column1 = table2.column1 INNER JOIN table3 ON table2.column2 = table3.column2;

When using joins, it's important to specify the related columns properly to ensure the correct results are returned.

Additionally, PostgreSQL provides the ability to use aliases for table names to make your SQL queries more readable.

Joins are a powerful tool for combining data from different tables to answer complex questions and retrieve meaningful insights from your database.

Enroll Now

• SQL
• DBMS