SQL JOIN

Structured Query Language (SQL) is a popular programming language used for managing relational databases. One of the key features of SQL is the ability to combine data from multiple tables using joins. In this blog post, we will explore SQL joins and their various types.

What is SQL Joins

SQL Join is a clause used in Structured Query Language (SQL) to combine rows from two or more tables based on a related column between them. It's used to retrieve data that is stored across multiple tables in a relational database.

Type of join

· Inner join

· Left join

· Right Jion

· Outer join

1. Inner join

This JOIN returns all records from multiple tables that satisfy the specified join condition. It is the simple and most popular form of join and assumes as a default join. If we omit the INNER keyword with the JOIN query, we will get the same output.

The SQL INNER JOIN would return the records where table1 and table2 intersect.

INNER JOIN Syntax

The following syntax illustrates the use of INNER JOIN in SQL Server

SELECT columns FROM table1 INNER JOIN table2 ON condition1 INNER JOIN table3 ON condition2 ;

2. Left Outer join

Another type of join is called a LEFT OUTER JOIN. This type of join returns all rows from the LEFT-hand table specified in the ON condition and only those rows from the other table where the joined fields are equal (join condition is met).

The SQL LEFT OUTER JOIN would return the all records from table1 and only those records from table2 that intersect with table1.

Syntax

The syntax for the LEFT OUTER JOIN in SQL is:

SELECT columns FROM table1 LEFT [OUTER] JOIN table2 ON table1.column = table2.column;

3. Right Outer join

Another type of join is called a SQL RIGHT OUTER JOIN. This type of join returns all rows from the RIGHT-hand table specified in the ON condition and only those rows from the other table where the joined fields are equal (join condition is met).

Syntax

The syntax for the RIGHT OUTER JOIN in SQL is:

SELECT columns FROM table1 RIGHT [OUTER] JOIN table2 ON table1.column = table2.column;

In some databases, the OUTER keyword is omitted and written simply as RIGHT JOIN.

4. Full Outer Join

Another type of join is called a SQL FULL OUTER JOIN. This type of join returns all rows from the LEFT-hand table and RIGHT-hand table with NULL values in place where the join condition is not met.

Syntax

The syntax for the SQL FULL OUTER JOIN is:

SELECT columns FROM table1 FULL [OUTER] JOIN table2 ON table1.column = table2.column;

The SQL FULL OUTER JOIN would return the all records from both table1 and table2.