Software Testing Models



SQL JOIN's

The JOIN is very much misunderstood term for beginners while learning the SQL commands. In the interview at least one question is ask about the SQL JOIN’s. So in this article I am trying to simplify the things for new SQL learners & make it easy to understand the SQL JOIN’s. 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’s.

 

What is SQL JOIN’s?

In the expert level JOIN’S are more common SQL commands used in day to day life. JOIN’s are used to retrieving the records from multiple tables. SQL allows you to take JOIN’s on multiple tables from same database as well as different databases from same server.

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

 

Why should we use JOIN’s?

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

 

Types of SQL JOINs:

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

  • 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 in 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 in 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 joins.

 

Let’s go ahead & learn different types of JOIN queries with practical example. Following are the three tables, Customers Product & Order.

sql-joins-tables

 

SQL INNER JOIN Keyword:

The INNER JOIN is selects all rows from both tables as sql query match the specified condition.

 

SQL INNER JOIN Syntax:

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

or

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

 

SQL INNER JOIN Query

 

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

 

INNER JOIN Query Example:

Suppose, the dealer want 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 INNER JOIN Query Result

 

SQL LEFT JOIN Keyword:

LEFT JOIN returns all records/rows from 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;

or

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

 

SQL LEFT OUTER JOIN Query

 

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 the all records from the Product table & NULL values are displayed for all records where no matching records found in Order table.

 

SQL LEFT OUTER JOIN Query Result
 

SQL RIGHT JOIN Keyword:

RIGHT JOIN is exact opposite to LEFT JOIN, it returns all records/rows from 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;

or

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

 

SQL RIGHT OUTER JOIN Query

 

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 the all records from the Order table & NULL values are displayed for all records where no matching records found in Product table. But if all records are matched then it will left table then no NULL records are returned in result set.

 

SQL RIGHT OUTER JOIN Query Result

 

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 the 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;

 

SQL FULL OUTER JOIN Query

 

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:

 

SQL FULL OUTER JOIN Query Result

 

Conclusion on SQL JOINS:

Learn SQL JOIN's

  • JOIN’s are 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’s are better than sub queries.
  • In the OUTER JOIN’S 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’s like GROUP BY, WHERE, SUB QUERIES, AGGREGATE FUNCTIONS etc.
Enter your email address:

Check email in your inbox for confirmation to get latest updates Software Testing for free.

Happy Testing!!!

1 comment to SQL JOIN’s

  • Pravin V. Kumbhare

    Yes I am completely satisfied with join explanations with given examples. Now no need to refer any other notes.

    can u pls explain Self Join as well with example.

Leave a Reply

  

  

  


*

You can use these HTML tags

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>