SQL JOIN IN PYTHON

Claudio Ctin4 months ago6 mins

In SQL, the JOIN clause is used to combine rows from two or more tables based on a related column between them.

Types of JOINs
1. INNER JOIN
An **INNER JOIN returns only the rows that have matching values in both tables.
SYNTAX

 SELECT columns
 FROM table1
 INNER JOIN table2
 ON table1.common_column = table2.common_column;

_EXAMPLE_

 SELECT employees.name, departments.department_name
 FROM employees
 INNER JOIN departments
 ON employees.department_id = departments.id

EXPLANATIONS
**This query selects all employees and their corresponding department names where there is a match between employees.department_id and departments.id.

2. LEFT JOIN (or LEFT OUTER JOIN)
**A LEFT JOIN returns all rows from the left table (table1), and the matched rows from the right table (table2). If no match is found, NULL values are returned for columns from the right table.

_SYNTAX_

 SELECT columns
 FROM table1
 LEFT JOIN table2
 ON table1.common_column = table2.common_column;

_EXAMPLE_

 SELECT employees.name, departments.department_name
 FROM employees
 LEFT JOIN departments
 ON employees.department_id = departments.id;

EXPLANATIONS

**This query selects all employees, including those who do not belong to any department. For employees without a department, department_name will be NULL.

3.RIGHT JOIN (or RIGHT OUTER JOIN)

**A RIGHT JOIN returns all rows from the right table (table2), and the matched rows from the left table (table1). If no match is found , NULL values are returned for columns from the left table.

_SYNTAX_

 SELECT columns
 FROM table1
 RIGHT JOIN table2
 ON table1.common_column = table2.common_column;

EXAMPLE

 SELECT employees.name, departments.department_name
 FROM employees
 RIGHT JOIN departments
 ON employees.department_id = departments.id;

_EXPLANATIONS_

**This query selects all departments, including those without any employees. For departments without employees, name will be NULL.

4.FULL JOIN (or FULL OUTER JOIN)

**A FULL JOIN returns all rows when there is a match in either left (table1) or right (table2) table. Rows without a match in one of the tables will have NULLs for columns from that table.

SYNTAX

 SELECT columns
 FROM table1
 FULL JOIN table2
 ON table1.common_column = table2.common_column;

EXAMPLE

 SELECT employees.name, departments.department_name
 FROM employees
 FULL JOIN departments
 ON employees.department_id = departments.id;

_EXPLANATIONS_

 This query selects all employees and departments, showing NULLs where there is no match.

5.CROSS JOIN
**A CROSS JOIN returns the Cartesian product of the two tables, i.e., it returns all possible combinations of rows from the tables.

_SYNTAX_

 SELECT columns
 FROM table1
 CROSS JOIN table2;

_EXAMPLE_

 SELECT employees.name, departments.department_name
 FROM employees
 CROSS JOIN departments;

EXPLANATIONS
**This query combines each employee with each department, resulting in a large number of rows.

6.SELF JOIN
**A SELF JOIN is a regular join, but the table is joined with itself.

SYNTAX

 SELECT a.columns, b.columns
 FROM table a, table b
 WHERE a.common_column = b.common_column;

EXAMPLE

 SELECT e1.name AS Employee, e2.name AS Manager
 FROM employees e1
 INNER JOIN employees e2
 ON e1.manager_id = e2.id;

Key Points:
i. JOIN Conditions:The ON clause specifies the condition for the join, typically matching a column from one table with a column from another table.
ii. Aliases:Using table aliases (e.g., e1, e2 in the SELF JOIN example) can make the query more readable and manageable, especially for self joins or when the same table is used multiple times.
iii. NULL Handling:In OUTER JOINs (LEFT, RIGHT, FULL), unmatched rows will have NULL values for the columns of the table that does not have the match.

Please follow and like us: