Difference Between Joins In SQL With Example

Joins are one of the most important and most commonly used query in SQL , it is used to retrieve data from multiple tables which is one of the most common task of any data analyst or data scientist.

So in this article we will explore all types of Joins in SQL and we will try to understand Difference Between Joins In SQL With Example.

We will even understand which join to use in which condition and what output will you get for each join.

So without delay let us get started to understand the Difference Between Joins In SQL With Example.

Difference Between Joins In SQL With Example

1. INNER JOIN –

It gives us the common data between two or more tables on which inner join is applied and it will exclude the rows where there is no match.

• When to use: When you want to find common records between two tables.

• Purpose: Returns rows that have matching values in both tables.

Example : We need customers and their orders id from two tables and each table have common row that is customer id .

Now below query will return a list of customers and their orders, where the customer ID matches in both the customers and orders tables.

2. LEFT JOIN

A left join will give us all the rows from left table and matching rows from right table and if there is no match in right table there the output values in that row will be null.

• Purpose: Returns all rows from the left table, even if there are no matches in the right table.

• When to use: When you want to find all records from one table, even if there are no matches in the other.

Example : We need customers and their orders id from two tables and each table have common row that is customer id but there are some missing order id’s for customer so in output there will be “null”.

This query will return all customers, including those who have no orders.  

3. RIGHT JOIN

As the name suggests a right join will give us all the rows from right table and matching rows from left table and in the rows where there is no data to match it will give us output as null.

It is just opposite to Left join.

• Purpose: Returns all rows from the right table, even if there are no matches in the left table.
• When to use: Similar to LEFT JOIN, but with the tables reversed.

Example :

Following query is just opposite to what we saw in above left query it will give all the row from right table but only matching rows from left table and null values where there is no value in left table.

4. FULL OUTER JOIN

You will use full outer join when you want to returns all rows when there is a match in either table. If there is no match, it returns NULL for the unmatched rows.

• Purpose: Returns all rows when there is a match in either left or right table.
• When to use: When you want to combine all rows from both tables, regardless of whether there is a match.

Example : When you need the complete data from both table no matter if there is any match in customer or order table.

Following query will return all customers and all orders, including those without matches in the other table.

5. CROSS JOIN

Cross join is just like cartesian product and it will return data in such a way that every row of first column will be combined two second table and then output will be given .

• Purpose: Returns all possible combinations of rows from the joined tables.
• When to use: When you need to generate all combinations of data from two tables, regardless of any matching criteria.

Example : If there are 4 rows in first column and 4 rows in second column then it every row from first table will connect with second table and give output .

Below query will combine every customer with every product, resulting in a list showing all possible customer-product combinations.

So if there are 4 rows in each table we will get 16 rows in output.

Related : Important SQL Topics For Data Interview

How To Know Which Join To Use In SQL

It might be confusing to decide when to use which join but following points will help you decide which join to use when.

• INNER JOIN: When you want to find records that have matching values in both tables. For example, to find customers who have placed orders.
• LEFT JOIN: When you want to find all records from the left table, even if there are no matches in the right table. For example, to find all customers, including those who haven’t placed any orders.
• RIGHT JOIN: When you want to find all records from the right table, even if there are no matches in the left table. For example, to find all products, including those that haven’t been sold.
• FULL OUTER JOIN: When you want to combine all rows from both tables, regardless of whether there is a match. For example, to find a list of all customers and products, including those without matches.
• CROSS JOIN: When you need to generate all possible combinations of data from two tables. For example, to create a lookup table of all possible product combinations.

Conclusion

Join are very important topic in SQL and it is very important to understand Difference Between Joins In SQL.

And our article on Difference Between Joins In SQL With Example will be helpful for you . But you will have thorough knowledge about the joins when you will practice them on daily basis.

So if this article informative and helpful for you then please do share it with others as well.