Joins in SQL

A JOIN clause in SQL is used to combine the records/rows from two or more tables, based on a common column that is present in the two tables. It creates a set that can be saved as a table or used as it is. They are predominantly used when a user is trying to extract data from tables that have one-to-many or many-to-many relationships between them.

Following are the types of SQL Joins:

 1. Inner Join

The Inner Join in SQL keyword selects all the common rows from each the tables which satisfies the given condition.

                                                 

 2. Left Join

This Left Join returns all the rows from the left table (Table 1) and the matched records from the right table (Table 2). The result is NULL from the right side table if there is no match in Table 2.

                                         

3. Right Join

This Right Join returns all the rows from the right table (Table 2) and the matched records from the left table (Table 1). The result is NULL from the left side table if there is no match in Table 1.

                                         

4. Full Join

Full Join shows the result set by combining the result of both the left join and right join. The result-set can contain all the rows from each of the tables. The rows for which there’s no matching, the result-set can contain NULL values.

                                         

5. Self Join

As the name says, self-join is part of a table is joined to itself based on a given condition, which is also called Unary relationships. To join a table itself means that each row of the table is combined with itself and with every other row of the table.

6. Cartesian Join

The Cartesian Join in SQL is also called Cross Join. In this, there’s a join for every row of one table to each row of another table. This usually happens once the matching column or where the condition isn’t specified. In general, if Table 1 has X rows and Table 2 has Y rows, the cross join will result in X * Y rows.

7. Union/Union ALL

SQL UNION is one of the set operations that give you the combined result of two SELECT statements into a single result set which includes all the rows that belong to the SELECT statements in the union. By default, the UNION operator removes all duplicate rows from the result sets. However, if you want to retain the duplicate rows, we need to specify the ALL keyword explicitly. In other words, the UNION  operator removes the duplicate rows while the UNION ALL operator includes the duplicate rows in the final result set.

UNION v/s JOIN

Thank You !!
Hope this was easy to understand and learn.