Table of content

JOINS Statement in SQL

A JOIN in SQL is a process of combining the data present in the two different tables to extract the desired output. There are different types of JOINS in SQL. They are,

  • INNER JOIN
  • LEFT JOIN
  • RIGHT JOIN
  • FULL JOIN

Let us understand the types of JOINS in SQL.

The INNER JOIN selects all rows that are common in both the tables as per the given condition.

inner-join-fig


Syntax of the INNER JOIN statement :

SELECT table1.column1,table1.column2,table2.column1,....
FROM table1 
INNER JOIN table2
ON table1.common_column = table2.common_column;

Example of the INNER JOIN Statement :

SELECT person_id,first_name,gender,age
FROM persons_table
INNER JOIN persons_table_2
ON persons_table.age=persons_table_2.age_2;

inner-join
In the above output, person_id, first_name, gender, age of both the tables are selected as per the given condition. The condition is, the common data which is present in the age of both the table are retrieved.

LEFT JOIN:

The LEFT JOIN selects all the data present in the left table and the data that matches the right table. The rows for which there is no matching, the retrieved data will contain NULL values.
LEFT-JOIN-FIG
The colored part indicates the data retrieved when we use the LEFT JOIN.

Syntax of the LEFT JOIN :

SELECT table1.column1, table2.column2...
FROM table1
LEFT JOIN table2
ON table1.common_column = table2.common_column;

Example of the LEFT JOIN :

SELECT person_id,first_name,age,persons_id_2,first_name_2,age_2
FROM persons_table
LEFT JOIN Persons_table_2
ON persons_table.age=Persons_table_2.age_2;

left-join-statement
In the above statement, person_id, first_name, age of both the tables are selected as per the given condition. The condition is the age of the persons_table.
Here all the rows from the persons_table is retrieved because it is a left table. Along with that, the age_2 of the persons_table_2 is retrieved which matches the value of the age column present in the persons_table.

Here, the meaning of the null in the above output is data not contained in the particular cell.

RIGHT JOIN statement in SQL:

The RIGHT JOIN selects all the data present in the right table and the data from the left table that matches the right table.
RIGHT-JOIN-FIG
The colored part indicates the data retrieved when we use the RIGHT JOIN.

Syntax of the RIGHT JOIN :

SELECT table1.column1, table2.column2...
FROM table1
RIGHT JOIN table2
ON table1.common_column = table2.common_column;

Example of the RIGHT JOIN :

SELECT person_id,first_name,age,persons_id_2,first_name_2,age_2
FROM persons_table
RIGHT JOIN Persons_table_2
ON persons_table.age=Persons_table_2.age_2;

right-join-statement
In the above output, person_id, first_name, age of both the tables are selected as per the given condition. The condition is, the age_2 of the persons_table_2.
Here all the rows from the persons_table are retrieved because it is the right table. Along with that, the age of the persons_table is retrieved in which matches the value of age_2 column is present in the persons_table_2.

FULL JOIN statement in SQL:

FULL JOIN retrieves the data by combining the result of both LEFT JOIN and RIGHT JOIN. The retrieved data will contain all the rows from both the tables.
FULL-JOIN-FIG
The colored part indicates the data retrieved when we use the FULL JOIN.

Syntax of the FULL JOIN statement:

SELECT table1.column1, table2.column2...
FROM table1
FULL JOIN table2
ON table1.common_column = table2.common_column;

Example of the FULL JOIN statement :

SELECT person_id,first_name,age,persons_id_2,first_name_2,age_2
FROM persons_table
FULL JOIN Persons_table_2
ON persons_table.age=Persons_table_2.age_2;

FULL-JOIN-STATEMENT
In the above output, person_id, first_name, age of both the tables are selected as per the given condition. The condition is, the age_2 of the persons_table_2.

And, the rows for which there is no matching, the retrieved data will contain NULL values. Here, both LEFT JOIN and the RIGHT JOIN involves together.

0 results
Comment / Suggestion Section
Point our Mistakes and Post Your Suggestions