SELECT data from Multiple Tables in SQL

The statement is used to retrieve the fields from multiple tables and with the help of JOIN operations we easily fetch the records from multiple tables, Generally JOINS are used when there are common records between two tables. In this article, we will look into various types of JOIN that are used in SQL.

SQL Joins

SQL joins combine two or more tables based on a common field between them. Here we are describing different types of Joins that are used in SQL below.

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

Syntax

SELECT tablename1.colunmnname, tablename2.columnname FROM tablename1

JOIN tablename2

ON tablename1.colunmnname = tablename2.columnname

ORDER BY columnname;

LEFT JOIN

In SQL, the Left join is used to fetch all the data from the Left table and common from both tables. If there are no matching records present in the left table then it returns the NULL value.

Syntax

SELECT column_name(s)

FROM table_nameA

LEFT JOIN table_nameB ON table_nameA.column_name = table_nameB.column_name;

RIGHT JOIN

In SQL, Right join is used to fetch all the data from the Right table and common from both tables. If there are no matching records present in the right table then it returns the NULL value.

Syntax

SELECT column_name(s)

FROM table_nameA

RIGHT JOIN table_nameB ON table_nameA.column_name = table_nameB.column_name;

INNER JOIN

It is used to fetch the common data from the tables. It returns only that value when there is a matched column between them.

Syntax

SELECT column_name(s)

FROM table_nameA

INNER JOIN table_nameB ON table_nameA.column_name = table_nameB.column_name;

FULL JOIN

In SQL, FULL JOIN is used to fetch all the records from both tables. If there is no matches in one of the table then its return the NULL value.

Syntax

SELECT column_name(s)

FROM table_nameA

FULL JOIN table_nameB ON table_nameA.column_name = table_nameB.column_name;

Example:

Let us take three tables named Geeks1, Geeks2, and Geeks3.

Geeks1 Table:

Geeks1 Table

Geeks2 Table:

Geeks2 Table

Geeks3 Table:

Geeks3 Table

Example to select the records from multiple tables:

Query

SELECT Geeks3.GID, Geeks3.PID, 
       Geeks3.Asset, Geeks1.FirstName, 
       Geeks2.LastName  
FROM Geeks3
LEFT JOIN Geeks1 
ON Geeks3.GID = Geeks1.ID
LEFT JOIN Geeks2 
ON Geeks3.GID = Geeks2.ID  

Output

output

Conclusion

In this article, we have basically mentioned the SELECT data from multiples tables and JOINS, and their types.Both the Statement, whether the Select or the Joins, play an important role in the Structured query language (SQL). Select statements are used to fetch the records from multiple tables and with the help of joins statement we can fetch what type of records you have to need. we have discussed all JOINS operations and their syntax with examples. and we also mentioned the three tables and showed the joins operation on them.