Joins in PostgreSql
Joins in PostgreSQL 🚀
In PostgreSQL, joins are used to combine rows from two or more tables based on a related column between them. There are several types of joins, each serving different purposes based on how you want to retrieve data.
Here’s an overview of the most common types of joins in PostgreSQL:
1. INNER JOIN 🔗
An INNER JOIN returns rows that have matching values in both tables. If there’s no match, those rows are excluded.
Syntax:
SELECT column1, column2FROM table1INNER JOIN table2ON table1.common_column = table2.common_column;
Example:
SELECT orders.order_id, customers.customer_nameFROM ordersINNER JOIN customersON orders.customer_id = customers.id;
🔹 Only returns orders with corresponding customers.
2. LEFT JOIN (LEFT OUTER JOIN) 🔄
A LEFT 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 column1, column2FROM table1LEFT JOIN table2ON table1.common_column = table2.common_column;
Example:
SELECT employees.name, departments.department_nameFROM employeesLEFT JOIN departmentsON employees.dept_id = departments.id;
🔹 Returns all employees and their departments, with NULL for employees without a department.
3. RIGHT JOIN (RIGHT OUTER JOIN) 🔄
A RIGHT JOIN is similar to a LEFT JOIN, but it returns all rows from the right table and the matching rows from the left table. If there’s no match, NULL values are returned for columns from the left table.
Syntax:
SELECT column1, column2FROM table1RIGHT JOIN table2ON table1.common_column = table2.common_column;
Example:
SELECT employees.name, departments.department_nameFROM employeesRIGHT JOIN departmentsON employees.dept_id = departments.id;
🔹 Returns all departments and their corresponding employees, even if some departments have no employees.
4. FULL JOIN (FULL OUTER JOIN) 🌐
A FULL JOIN returns all rows from both tables. If there’s no match, NULL values are returned for the columns from the table that doesn’t have a match.
Syntax:
SELECT column1, column2FROM table1FULL JOIN table2ON table1.common_column = table2.common_column;
Example:
SELECT employees.name, departments.department_nameFROM employeesFULL JOIN departmentsON employees.dept_id = departments.id;
🔹 Returns all employees and all departments, with NULL values for unmatched rows.
5. CROSS JOIN ✖️
A CROSS JOIN returns the Cartesian product of both tables. Every row from the first table is combined with every row from the second table. This can generate a large number of rows.
Syntax:
SELECT column1, column2FROM table1CROSS JOIN table2;
Example:
SELECT employees.name, departments.department_nameFROM employeesCROSS JOIN departments;
🔹 Combines each employee with each department, regardless of whether they are related.
6. SELF JOIN 🔁
A SELF JOIN is a join where a table is joined with itself. This can be useful to compare rows within the same table.
Syntax:
SELECT column1, column2FROM table1 t1JOIN table1 t2ON t1.common_column = t2.common_column;
Example:
SELECT e1.employee_name AS Employee1, e2.employee_name AS Employee2FROM employees e1JOIN employees e2ON e1.manager_id = e2.id;
🔹 Finds employees and their managers by joining the employees table with itself.
7. NATURAL JOIN 🌱
A NATURAL JOIN automatically joins tables based on columns with the same name and compatible data types.
Syntax:
SELECT column1, column2FROM table1NATURAL JOIN table2;
Example:
SELECT employees.name, departments.department_nameFROM employeesNATURAL JOIN departments;
🔹 Automatically joins employees and departments on the common column (e.g., dept_id).
