Cyber Monday Deal : Flat 30% OFF! + free self-paced courses  - SCHEDULE CALL

- SQL Server Blogs -

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.

Learn SQL Server in the Easiest Way

  • Learn from the videos
  • Learn anytime anywhere
  • Pocket-friendly mode of learning
  • Complimentary eBook available

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.

SQL Server Training & Certification

  • No cost for a Demo Class
  • Industry Expert as your Trainer
  • Available as per your schedule
  • Customer Support Available

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:

Type of SQL 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: 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: How To Quickly Get Entry Level SQL Jobs

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.

SQL Server Training & Certification

  • Personalized Free Consultation
  • Access to Our Learning Management System
  • Access to Our Course Curriculum
  • Be a Part of Our Free Demo Class

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: How to Create Table in SQL Server by SQL Query?

SQL Tutorial Overview

fbicons FaceBook twitterTwitter lingedinLinkedIn pinterest Pinterest emailEmail

     Logo

    JanBask Training

    A dynamic, highly professional, and a global online training course provider committed to propelling the next generation of technology learners with a whole new way of training experience.


  • fb-15
  • twitter-15
  • linkedin-15

Comments

Trending Courses

Cyber Security Course

Cyber Security

  • Introduction to cybersecurity
  • Cryptography and Secure Communication 
  • Cloud Computing Architectural Framework
  • Security Architectures and Models
Cyber Security Course

Upcoming Class

2 days 14 Dec 2024

QA Course

QA

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

Upcoming Class

8 days 20 Dec 2024

Salesforce Course

Salesforce

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

Upcoming Class

2 days 14 Dec 2024

Business Analyst Course

Business Analyst

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

Upcoming Class

2 days 14 Dec 2024

MS SQL Server Course

MS SQL Server

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

Upcoming Class

1 day 13 Dec 2024

Data Science Course

Data Science

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

Upcoming Class

2 days 14 Dec 2024

DevOps Course

DevOps

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

Upcoming Class

5 days 17 Dec 2024

Hadoop Course

Hadoop

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

Upcoming Class

8 days 20 Dec 2024

Python Course

Python

  • Features of Python
  • Python Editors and IDEs
  • Data types and Variables
  • Python File Operation
Python Course

Upcoming Class

9 days 21 Dec 2024

Artificial Intelligence Course

Artificial Intelligence

  • Components of AI
  • Categories of Machine Learning
  • Recurrent Neural Networks
  • Recurrent Neural Networks
Artificial Intelligence Course

Upcoming Class

2 days 14 Dec 2024

Machine Learning Course

Machine Learning

  • Introduction to Machine Learning & Python
  • Machine Learning: Supervised Learning
  • Machine Learning: Unsupervised Learning
Machine Learning Course

Upcoming Class

15 days 27 Dec 2024

 Tableau Course

Tableau

  • Introduction to Tableau Desktop
  • Data Transformation Methods
  • Configuring tableau server
  • Integration with R & Hadoop
 Tableau Course

Upcoming Class

8 days 20 Dec 2024

Search Posts

Reset

Receive Latest Materials and Offers on SQL Server Course

Interviews