What are other types of SQL joins?

In addition to INNER JOIN, there are several other types of SQL joins, each serving different purposes:

1. LEFT JOIN (or LEFT OUTER JOIN):

   • Returns all records from the left table and the matched records from the right table. If there is no match, NULL values are returned for columns from the right table.
   • Example:

     SELECT a.*, b.*
     FROM table_a a
     LEFT JOIN table_b b ON a.id = b.a_id;

2. RIGHT JOIN (or RIGHT OUTER JOIN):

   • Returns all records from the right table and the matched records from the left table. If there is no match, NULL values are returned for columns from the left table.
   • Example:

     SELECT a.*, b.*
     FROM table_a a
     RIGHT JOIN table_b b ON a.id = b.a_id;

3. FULL JOIN (or FULL OUTER JOIN):

   • Returns all records when there is a match in either the left or right table. If there is no match, NULL values are returned for the non-matching side.
   • Example:

     SELECT a.*, b.*
     FROM table_a a
     FULL JOIN table_b b ON a.id = b.a_id;

4. CROSS JOIN:

   • Returns the Cartesian product of the two tables, meaning every row from the first table is combined with every row from the second table.
   • Example:

     SELECT a.*, b.*
     FROM table_a a
     CROSS JOIN table_b b;

5. SELF JOIN:

   • A join where a table is joined with itself. This is useful for comparing rows within the same table.
   • Example:

     SELECT a.*, b.*
     FROM employees a
     JOIN employees b ON a.manager_id = b.id;

Each type of join serves a specific purpose and can be used based on the requirements of your query.