Today's Offer - SQL Server Certification Training - Enroll at Flat 10% Off.

- SQL Server Blogs -

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

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 works 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.

We will start the discussion with a brief introduction to these three operators, and then we will try to understand the detailed differences between them.

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 either 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

Query:


Select Sr_Name From Supplier Where Exists (Select Pr_Name From Products Where Supplier_ID = Supplier.Supplier_ID And Price < 20);

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

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: DBMS Interview Questions

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 cost 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. Rest of the records are excluded from the result. Below is the diagrammatic representation of these joins: JOIN Clause 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: What is SQL Server? Microsoft SQL Server Tutorial Guide for Beginners

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. SQL Server quiz 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’;

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.

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: SQL Server Recovery Models-Simple, Full and Bulk Log

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. free SQL Server demo 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 returens 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

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. 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.

SQL Tutorial Overview


    Janbask Training

    JanBask Training is a leading Global Online Training Provider through Live Sessions. The Live classes provide a blended approach of hands on experience along with theoretical knowledge which is driven by certified professionals.


Trending Courses

AWS

  • AWS & Fundamentals of Linux
  • Amazon Simple Storage Service
  • Elastic Compute Cloud
  • Databases Overview & Amazon Route 53

Upcoming Class

4 days 24 Nov 2019

DevOps

  • Intro to DevOps
  • GIT and Maven
  • Jenkins & Ansible
  • Docker and Cloud Computing

Upcoming Class

5 days 25 Nov 2019

Data Science

  • Data Science Introduction
  • Hadoop and Spark Overview
  • Python & Intro to R Programming
  • Machine Learning

Upcoming Class

5 days 25 Nov 2019

Hadoop

  • Architecture, HDFS & MapReduce
  • Unix Shell & Apache Pig Installation
  • HIVE Installation & User-Defined Functions
  • SQOOP & Hbase Installation

Upcoming Class

6 days 26 Nov 2019

Salesforce

  • Salesforce Configuration Introduction
  • Security & Automation Process
  • Sales & Service Cloud
  • Apex Programming, SOQL & SOSL

Upcoming Class

14 days 04 Dec 2019

Course for testing

  • Salesforce Configuration Introduction
  • Security & Automation Process
  • Sales & Service Cloud
  • Apex Programming, SOQL & SOSL

Upcoming Class

34 days 24 Dec 2019

QA

  • Introduction and Software Testing
  • Software Test Life Cycle
  • Automation Testing and API Testing
  • Selenium framework development using Testing

Upcoming Class

13 days 03 Dec 2019

Business Analyst

  • BA & Stakeholders Overview
  • BPMN, Requirement Elicitation
  • BA Tools & Design Documents
  • Enterprise Analysis, Agile & Scrum

Upcoming Class

5 days 25 Nov 2019

SQL Server

  • Introduction & Database Query
  • Programming, Indexes & System Functions
  • SSIS Package Development Procedures
  • SSRS Report Design

Upcoming Class

8 days 28 Nov 2019

Comments

Search Posts

Reset

Receive Latest Materials and Offers on SQL Server Course

Interviews