Structured Query Language (SQL) is a standard language used to manage relational databases. One of the most important features of SQL is its ability to join tables. A join combines rows from two or more tables based on a related column between them. In this blog, we will cover the four types of joins in SQL: Inner, Outer, Left, and Right Joins.

Inner Join

An inner join returns only the rows that have matching values in both tables. The syntax of an inner join is as follows:

SELECT *
FROM table1
INNER JOIN table2
ON table1.column = table2.column;

For example, consider two tables, "customers" and "orders". The "customers" table has a "customer_id" column, while the "orders" table has a "customer_id" column. We can join these two tables on the "customer_id" column to get the customer details and their orders.

SELECT customers.customer_name, orders.order_id
FROM customers
INNER JOIN orders
ON customers.customer_id = orders.customer_id;

Outer Join

An outer join returns all the rows from one table and the matching rows from the other table. If there is no match, the null value is returned. The syntax of an outer join is as follows:

SELECT *
FROM table1
LEFT/RIGHT OUTER JOIN table2
ON table1.column = table2.column;

For example, consider two tables, "employees" and "departments". The "employees" table has a "department_id" column, while the "departments" table has a "department_id" column. We can join these two tables on the "department_id" column to get the employee details and their department details.

SELECT employees.employee_name, departments.department_name
FROM employees
LEFT OUTER JOIN departments
ON employees.department_id = departments.department_id;

Left Join

A left join returns all the rows from the left table and the matching rows from the right table. If there is no match, the null value is returned. The syntax of a left join is as follows:

SELECT *
FROM table1
LEFT JOIN table2
ON table1.column = table2.column;

Right Join

A right join returns all the rows from the right table and the matching rows from the left table. If there is no match, the null value is returned. The syntax of a right join is as follows:

SELECT *
FROM table1
RIGHT JOIN table2
ON table1.column = table2.column;

Conclusion

Joins are an essential feature of SQL, allowing you to combine data from multiple tables. In this blog, we covered the four types of joins in SQL: Inner, Outer, Left, and Right Joins. Understanding these joins and their syntax is crucial for building complex queries and working with relational databases.