How To Differentiate SQL Server JOIN, IN And EXISTS Clause?

Introduction

SQL Server is a relational database management system marketed by Microsoft. Like other relational database management systems, SQL Server is also built on the top of SQL as a standard programming language and used to interact with the relational databases. SQL is tied to the T-SQL that has a proprietary programming construct.

SQL Server has worked exclusively for the Microsoft environment for more than 20 years now. In 2017, it was made available for both Windows and Linux platforms. Today, in this blog, we will discuss three commonly used operators of SQL Server; these are IN, Exists and JOIN clauses and various comparative studies such as SQL Join vs Inner Join, SQL Join vs where, etc.

We will start the discussion with a brief introduction to these three operators, and then we will try to understand about the SQL join vs Union. T.

An Introduction to IN, EXISTS and JOIN Operators

SQL Server uses many operators, and three of the most popular are Exists, IN and JOIN clauses.  Let us discuss each of them one by one along with examples:

Exists Operator

Exists is a logical SQL operator that helps to check the sub-query result, either True or False. It is used to check whether a row is returned through this sub-query or not? If one or more rows are returned, then this operator returns True otherwise False when no rows are returned. To check how to use Exists operator in SQL, Click here.

Syntax: Select column_name(s) From table_name Where Exists (Select column_name From table_name Where condition);

Example: Let we have two tables for which we will use Exists operator:

Product Table

Product ID	Product Name	Unit	Price	Supplier ID
1	Chang	24-12 oz bottles	$10	2
2	Chais	48-6 oz jars	$22	1
3	Aniseed Syrup	12-550 ml	$18	14
4	Exotic Liquid	36 boxes	$19	3
5	Gumbo Box	10 boxes	$21.5	15

Supplier Table:

Supplier ID	Supplier Name	City	Postal Code
1	Tokyo Traders	London	100
2	Kelly’s Homestead	Tokyo	48104
3	Cajun Delight	New Orleans	70117
4	Exotic Liquid	Arbor	EC1 4SD

Select Sr_Name From Supplier Where Exists (Select Pr_Name From Products Where Supplier_ID = Supplier.Supplier_ID And Price 

Here, the SQL statement result is True, and it returns a list of products whose price is less than 20.

IN Operator

You can specify multiple values through IN operator and use WHERE clause along. It is also known as the shorthand for multiple OR conditions. The syntax of IN operator in SQL Server is:

Read: Top 50 Datastage Interview Questions and Answers

Select column_name(s) From table_name Where column_name IN (value1, value2, - - - - );

OR

Select column_name(s) From table_name Where column_name IN (Select Statement);

For Example, the above tables for the IN query can be used as:

Select * From Product Where Price IN ($10, $22, $18);

The above query will return all the products whose costs are either $10, $22 or $18. One more example of IN query is given below using the Supplier table:

Select * From Supplier Where City NOT IN (London, Tokyo);

Here, the result of the above query will be the name of all suppliers that are not living in London or Tokyo city.

JOIN Clause -

JOIN SQL operation is used to establish connections between two or more tables of a database. This join is performed by matching the columns of the two tables. Many complex problems of databases are solved by JOIN operation. JOIN clause is used to combine the rows of two or more tables; for this, there should be a common column between both the tables. SQL Joins are of following types:

• INNER JOIN (The matched values of both the tables are returned)
• LEFT OUTER JOIN (Result will include all records of the left table and matched records of the right table)
• RIGHT OUTER JOIN (Result will include all records of the right table and matched records of the left table)
• FULL OUTER JOIN (Result will have all matching records of either left or right table)

To check how SQL inner joins are different from SQL outer joins, click here. Three algorithms work behind these JOIN operations; these are hash join, nested join, and sort-join. The default JOIN type is INNER JOIN. In this JOIN, those records of two tables are selected whose values are matched. The rest of the records are excluded from the result. Below is the diagrammatic representation of these joins:

Let us apply the INNER JOIN clause on Product and Supplier tables to get the desired values of these tables. We can use the following syntax here: Select column_name(s) From table_name Inner Join table2_name On table_name.column_name = table2_name.column_name; Select Product.Product_ID, Supplier.Supplier_Name, Product.Price From Product Inner Join Suppliers.

To use and apply various types of JOINs, you must use the appropriate keywords in place of Inner Join.

• Inner Join
• Full Join
• Left Join
• Right Join

The rule is applied as per the type of Join. Let us apply the Join on two tables that are product and supplier:

Select Product.Product_Id, Product.Price, Product.Pr_name Inner Join Product On Product.Supplier_iD== Supplier.Support_ID;

Here, the above query displays the Product id, price, and name of those products that have the same supplier_id. Moreover, here rows that have the same supplier id and the rest details will not be displayed.

Read: Top 30 Data Modeling Interview Questions with Answers

A Comparison of Join, Exists and IN SQL Server Operators

Many of the SQL Server users know the syntax of In, Join and Exists clauses. They know the working of In clause, but do not have a clear understanding of Exists and Join clause. We can make this difference clear by writing a similar query for all of these three clauses. For this, let us take the example of the following tables:

Table A                                                     Table B

Id        Name	Id       Title
1          Rob
2          Kenny	1               Manager
2          Anny	2               Sales
4          John	3               Analyst
3          Greg

Here to know the name of the analysts, we can apply the following query:

Select * from tableA where tableA. Id IN (Select tableB.id From tableB where title=’Analyst’);

Here, the output of this query will be just a single record that is Greg For those who are not familiar with the SQL syntax, it could be quite easier to understand this. Through this query, the IN clause is being used that will compare the values from each table and display all values from table A that have the same id as of title Analyst, i.e. 3. However, IN statement is quite clear but often it is found less efficient than Join and Exists clauses. The same result can be produced through the following queries as well:

Using EXISTS:

Select * from tableA Where EXISTS (Select 1 From tableB Where title=’Analyst’ And tableA.id=tableb.id);

Using JOINS (Inner Join is the default join when the name is not specified):

Select * from tableA JOIN tableB ON tableA.id=tableB.id Where tableB.title = ‘Analyst’;

SQL Join vs Subquery and SQL Join vs Where

It has been seen that in several cases EXISTS and JOIN are much more efficient than IN clause. Well, you want to know why and how then we have listed it below:

When an IN clause is being used by combining it with a subquery, then the database will process the entire subquery result firstly and after that it will process the result of the query as a whole, as per the result of matching the query.

Read: Comparative Study of SQL and NoSQL Databases

In case of Exists and Join clause, when such query will be executed the result of the query will be returned either True or False. For the large tables in the subquery, Exists and Join clauses perform well.

Moreover, Join also gives more flexibility to return all of the employees that either have or do not have the desired or specified title. Following query is used to display the title of employees:

Select * From tableA Join tableB ON tableA.id = tableB.id;

The output of this query will be: 1     Rob 1 Manager 2     Kenny   2 Sales 2     Anny 2 Sales 3     Greg 3 Analyst To Display the Name of Employees that do not have any title, we can use the following query:

Read: What is the SQL Insert Query? How to Insert (Date, Multiple Rows, and Values in Table)

Select * From tableA LEFT JOIN tableB ON tableB ON tableA.id = tableB.id Where tableB.id IS NULL;

The output of this query will be:

4  John  NULL NULL Here, only John does not have any title. Moreover, if we want to enlist this value through JOIN query, then we can use LEFT JOIN clause instead of INNER JOIN, and it will be displayed with NULL data through this query.

If you have used lots of IN statements throughout your code, then you can compare the performance of IN clause with JOIN and EXISTS operators and then you can use it at the appropriate location. For those who still have the misconception that IN behaves same as EXISTS and JOIN in terms of the returned result, they can see here that this is simply not true and it depends on the use of clause. The one-liner interpretation of these subqueries looks like this:

• IN: TRUE value is returned if and only if a specified value matches the value of any sub-query or table
• EXISTS: if the subquery contains any row then only it returns TRUE
• JOIN: it can join the columns of two result-sets on specified joining

Well, these statements may look quite similar, but in reality when they will be applied to the tables or the sub-queries, then the internal behavior of these statements is found quite different.

Final Words

I hope this blog illustrating SQL join vs union, SQL join vs subquery etc was helpful to you.  As we have seen in this blog that all the three clauses - JOIN, IN and EXISTS can be used for the same purpose, but they differ in their internal working. We can say that their logical working is different. If you are likely to attend an interview on SQL server related profile you must know about the SQL join vs union and the other related concepts such as SQL inner vs outer join, SQL join vs subquery, SQL jon vs where etc.

You can select any of them as per your requirement. Moreover, for the large tables, it is good to use either JOIN of EXISTS rather than IN. To learn more about operators, you can join an online learning platform too. 

Read: Different Types of SQL Server & SQL Database Functions

FaceBook Twitter Google+ LinkedIn Pinterest Email