SQL JOIN in Database Testing

STC Admin

11 years ago

The JOIN is very much misunderstood term for beginners while learning the SQL commands. In the interview, at least one question is to ask about the SQL JOIN. So in this article, I am trying to simplify the things for new SQL learners & make it easy to understand the SQL JOIN. At the end of this post, you should in a position to write your SQL JOIN query independently. So first start from the basics of JOIN.

What is SQL JOIN?

At the expert level, JOIN is more common SQL commands used in day to day life. JOIN is used to retrieving the records from multiple tables. SQL allows you to take JOIN on multiple tables from the same database as well as different databases from the same server.

Basically the tables are interrelated with each other using Primary & foreign keys. So these keys are used in the JOIN to interlink two tables.

Why should we use JOIN?

Many times you are thinking “Why to use SQL JOIN” as the same task can be done using different queries. In the database, queries are executed one by one & the result of the successive query can be used for the next query. If we use the JOIN queries then instead of processing multiple queries SQL server process only a single query which reduces the SQL server overhead. The main advantage of SQL JOIN is improved performance. Also using multiple queries lead to more data transfer from SQL server to the application which reduces the performance.

Types of SQL JOIN

Before we start learning SQL JOIN, we will see the list of different types of SQL JOIN:

INNER JOIN: The INNER JOIN keyword selects all rows from both tables as long as there is a match between the columns in both tables (Table1 and Table2).
LEFT JOIN: The LEFT JOIN keyword returns all rows from the left table (Table1), with the matching rows in the right table (Table2). The result is NULL on the right side when there is no match.
RIGHT JOIN: The RIGHT JOIN keyword returns all rows from the right table (Table2), with the matching rows in the left table (Table1). The result is NULL on the left side when there is no match.
FULL JOIN: The FULL OUTER JOIN keyword returns all rows from the left table (Table1) and from the right table (Table2). The FULL OUTER JOIN keyword combines the result of both LEFT and RIGHT JOIN.

Let’s go ahead & learn different types of JOIN queries with a practical examples. The following are the three tables, Customers Product & Order.

SQL INNER JOIN Keyword

The INNER JOIN selects all rows from both tables as SQL query matches the specified condition.

SQL INNER JOIN Syntax

SELECT column_name(s)
FROM Table1
JOIN Table2
ON Table1.column_name=Table2.column_name;

SELECT column_name(s)
FROM Table1
INNER JOIN Table2
ON Table1.column_name=Table2.column_name;

In the query you can use JOIN or INNER JOIN, both are the same.

INNER JOIN Query Example

Suppose, the dealer wants the list of order details like Product name, Unit Price, Quantity & Price. So, in this case, you can use INNER JOIN to get the records from both Product & Order tables. The ProductId is common in both tables. So in the INNER JOIN condition is added on ProductId & only matching records from Product & Order tables are returned.

SELECT T2.OrderID, T1.ProductID, T1.Name, T1.UnitPrice, T2.Quantity, T2.Price
FROM Product AS T1
INNER JOIN Order AS T2 ON T1.ProductID = T2.ProductID
ORDER BY T2.OrderID

Following is the result upon executing the above SQL INNER JOIN query:

SQL LEFT JOIN Keyword

LEFT JOIN returns all records/rows from the left table and from right table returns only matched records. Where no matches have been found in the table on the right, NULL is returned.

SQL LEFT JOIN Syntax

SELECT column_name(s)
FROM Table1
LEFT JOIN Table2
ON Table1.column_name=Table2.column_name;

SELECT column_name(s)
FROM Table1
LEFT OUTER JOIN Table2
ON Table1.column_name=Table2.column_name;

LEFT JOIN query Example

SELECT T2.OrderID AS OrderID, T1.ProductID, T1.Name, T1.UnitPrice, T2.Quantity AS Quantity, T2.Price AS Price
FROM Product AS T1
LEFT OUTER JOIN Order AS T2 ON T1.ProductID = T2.ProductID
ORDER BY T1.ProductID

Following is the result upon executing the above SQL LEFT OUTER JOIN query. You can see all records from the Product table & NULL values are displayed for all records where no matching records found in the Order table.

SQL RIGHT JOIN Keyword

RIGHT JOIN is the exact opposite to LEFT JOIN, it returns all records/rows from the right table and from left table returns only matched records. Where no matches have been found in the table on the left, NULL is returned.

SQL RIGHT JOIN Syntax

SELECT column_name(s)
FROM Table1
RIGHT JOIN Table2
ON Table1.column_name=Table2.column_name;

SELECT column_name(s)
FROM Table1
RIGHT OUTER JOIN Table2
ON Table1.column_name=Table2.column_name;

RIGHT JOIN query Example

SELECT T2.OrderID AS OrderID, T1.ProductID, T1.Name, T1.UnitPrice, T2.Quantity AS Quantity, T2.Price AS Price
FROM Product AS T1
RIGHT OUTER JOIN Order AS T2 ON T1.ProductID = T2.ProductID
ORDER BY T1.ProductID

Following is the result upon executing the above SQL RIGHT JOIN query. You can see all records from the Order table & NULL values are displayed for all records where no matching records found in the Product table. But if all records are matched then it will left table then no NULL records are returned in result set.

SQL FULL OUTER JOIN Keyword

FULL OUTER JOIN combines left outer join and right outer join. This join returns all records/rows from both the tables. If there are no columns matching in both tables, it returns NULL values.

SQL FULL OUTER JOIN Syntax

SELECT column_name(s)
FROM Table1
FULL OUTER JOIN Table2
ON Table1.column_name=Table2.column_name;

FULL OUTER JOIN query Example

Suppose we want to get all Order records against all the Product records, then we can use the FULL OUTER JOIN script shown below to get all records from both tables.

SELECT T2.OrderID AS OrderID, T1.ProductID, T1.Name, T1.UnitPrice, T2.Quantity AS Quantity, T2.Price AS Price
FROM Product AS T1
FULL OUTER JOIN Order AS T2 ON T1.ProductID = T2.ProductID
ORDER BY T1.ProductID

Following is the result upon executing the above SQL FULL OUTER JOIN query:

Conclusion on SQL JOIN

JOIN is used to combine & get the data from different tables.
INNER JOIN returns rows when there is a match in both tables.
LEFT JOIN returns all rows from the left table, even if there are no matches in the right table.
RIGHT JOIN returns all rows from the right table, even if there are no matches in the left table.
FULL JOIN returns rows when there is a match in one of the tables.
The performances of JOIN is better than subqueries.
In the OUTER JOIN when no records are returned then NULL values are returned in the result set.
JOIN queries can be used with the conjunction of SELECT, INSERT, UPDATE, DELETE commands.
Also, we can use different clauses along with JOIN like GROUP BY, WHERE, SUB QUERIES, AGGREGATE FUNCTIONS, etc.

⇓ Subscribe Us ⇓

If you are not regular reader of this website then highly recommends you to Sign up for our free email newsletter!! Sign up just providing your email address below:

Happy Testing!!!