JOIN in SQL

 

JOIN

In SQL, a JOIN is used to combine rows from two or more tables based on a related column between them. It allows you to query data from multiple tables in a single statement by specifying how the tables are related.

Types of JOINs in SQL:

1. INNER JOIN:

   • Retrieves rows that have matching values in both tables.
   • Example:

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

2. LEFT JOIN (or LEFT OUTER JOIN):

   • Retrieves all rows from the left table and the matching rows from the right table. If no match is found, NULL values are returned for columns from the right table.
   • Example:

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

3. RIGHT JOIN (or RIGHT OUTER JOIN):

   • Retrieves all rows from the right table and the matching rows from the left table. If no match is found, NULL values are returned for columns from the left table.
   • Example:

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

4. FULL JOIN (or FULL OUTER JOIN):

   • Retrieves rows when there is a match in either table. Rows with no match in one table will include NULL values for the missing columns.
   • Example:

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

5. CROSS JOIN:

   • Produces a Cartesian product of the two tables, combining each row from the first table with every row from the second table.
   • Example:

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

6. SELF JOIN:

   • A table is joined with itself, often to compare rows within the same table.
   • Example:

     SELECT a.name AS Employee1, b.name AS Employee2
     FROM employees a
     INNER JOIN employees b
     ON a.manager_id = b.id;
     

Key Points:

• The type of join you use depends on the relationship between the tables and the desired result.
• ON clause specifies the condition for the join, and USING clause can be used if the column names are the same in both tables.