Introduction to SQL Joins

Purpose of SQL Joins:

Join is the most important concept in SQL. Join is used for getting data from two or more tables however it is observed that people do not understand the right concept of the join and they end up using the wrong join method which might result in the wrong dataset.

Use of Join:

A SQL Join statement is used to combine data from two or more tables based on a common field between them. When we have a large web application and data are huge in multiple tables then we need to combine the data of two or more tables. In such a case, we need to use join queries.

Types of Join:

• Left Join
• Right Join
• Full Outer Join
• Inner Join
• Cross Join
• Self Join

Here, we are using below two tables for Join examples.

Examples Tables:

Customers:

Orders:

Left Join:

This join returns all the values which are located in the left table and matched values that are located in the right table. select cs.id, cs.name, ord.date from customers cs left join orders ord on cs.id = ord.customer_id

Description:

The above query returns all records from the customer’s tables and matched records from the orders table. select cs.id, cs.name, ord.date from orders ord left join customers cs on cs.id = ord.customer_id

Right Join:

This join returns all the values which are located in the right table and unmatched values which are located in the left table select cs.id, cs.name, ord.date from customers cs right join orders ord on cs.id = ord.customer_id

Description:

The above query returns all records from orders tables and unmatched records from customers table. select cs.id, cs.name, ord.date from orders ord right join customers cs on cs.id = ord.customer_id

Inner Join:

It is used to return only those rows from the tables that matched the specified condition. it returns the common rows from two tables. select cs.id, cs.name, ord.date from customers cs inner join orders ord on cs.id = ord.customer_id

Full Outer Join:

Full outer join returns all the rows from the left table and from the right table. The resultant table contains all the records from both the tables and fill the un-matched fields with NULL select cs.id, cs.name, ord.date from customers cs full outer join orders ord on cs.id = ord.customer_id

Cross Join:

Cross Join also called Cartesian Join. It is used to return all the records where each row from the first table is combined with each row of the second table. select cs.id, cs.name, ord.date from customers cs cross join orders ord

Self Join:

Self Join means a table is joined with itself. A self-join can be used to find the record that matches another record in the same table. It uses the same syntax as a join but the only difference is that the same table is used instead of a different table select cs.id, cs.name from customers cs1, customers cs2 where cs1.state= cs2.state

If you have any queries then feel free to ask in the comment section below.