Difference Between JOIN, IN and EXISTS Clause in SQL

SEQUEL widely known as SQL, Structured Query Language is the most popular standard language to work on databases. We can perform tons of operations using SQL which includes creating a database, storing data in the form of tables, modify, extract and lot more. There are different versions of SQL like MYSQL, PostgreSQL, Oracle, SQL lite, etc.  There are some important commands in SQL which can be broadly categorized into DDL, DML, DCL, TCL, DQL. 

In this article we are going to differentiate among three different mostly used commands in SQL:

• IN
• EXISTS
• JOIN

1. IN OPERATOR: It is mostly used with WHERE clause to test for a given expression or a record it matches with a particular value from a set of values. It works like a multiple OR operator. The negation of IN operator is NOT IN which helps to execute the rows which doesn’t match the set of values. 

Syntax:

SELECT col_name(s)

FROM Table_name

WHERE col_name IN(val1, val2, val3, …..)

Where col_name is the name of the column(s) and val is the set of values

Example: 

Geeks Student
Stud_ID	Name	Location
1	Rohit	Guwahati
2	Neha	Patna
3	Ramesh	Noida
4	Jignesh	Ahmedabad
5	Raj	Guwahati

To fetch the details of STUDENTS hailing from Guwahati and Patna:

2. EXISTS OPERATOR: It is mainly used in the nested query (query inside a query). It is used to check the existence of the record provided in the subquery. It returns Boolean TRUE if one or more records match else FALSE when no rows match. Similarly, like IN operator, here we can also use the negation of EXISTS known as the “NOT EXISTS” operator.

Syntax: 

SELECT col_name(s) 

FROM Table_Name

WHERE EXISTS 

(SELECT col_name(s) 

FROM Table_Name

WHERE condition);

Where condition: the condition should also contain column_matching information with common attribute if two or more tables are used.

Example:

Purchase Table:

Customer Table:

To fetch the details of NAME and EMAIL ADDRESS of customers who purchased Mobile Phone from an E-Commerce site.

3. JOIN: It is used to concatenate tuples or rows of two or more tables on the basis of some matching column. It returns NULL value if any entry doesn’t match in both the tables. There are basically four types of JOINS present in SQL:

1. INNER JOIN: Values matched in both tables are returned.
2. LEFT JOIN: All records in left table (Table1) and the rows matched in the right table (Table2).
3. RIGHT JOIN: All records in right table (Table2) and the rows matched in the left table (Table1).
4. FULL OUTER JOIN: All records matching from either Table1 or Table2.

Syntax:

SELECT * FROM Table1

(JOIN_TYPE) Table2

ON Table1.col_match = Table2.col_match

Where, JOIN_TYPE is the type of JOIN to perform whether INNER, LEFT, RIGHT, FULL. And col_match is the matching columns in both the tables.

For the above same tables ‘Purchase Information’, ‘Customer Information’ let us look at what different JOIN operators perform:

• Using INNER JOIN:

• Using LEFT JOIN:

• Using RIGHT JOIN:

• Using FULL JOIN:

Difference Between JOIN, IN, and EXISTS clause in SQL

IN	EXISTS	JOINS
It works like a multiple OR operator. So, it exempts us to write OR multiple times in a query.	It returns TRUE value if matching is found.	It is used to join two or more tables into a single table.
All the values inside IN operator will be scanned and then decision is made.	If we get TRUE value for a single condition, it will stop its execution.	It will first check whether matching takes place or not and then join the two tables on the basis of matching columns in both tables.
It returns TRUE, FALSE as well as NULL values.	It returns either TRUE or FALSE.	It returns NULL entry in joined table if matching is not present.
It can be used both in nested queries as well as with values as we have seen in the example above.	It is only used on nested queries.	JOIN can also be used with nested queries.
It is less efficient when IN is used with subquery because the entire subquery will execute first by the relational database and then the final execution takes place on the basis of condition specified. However, for larger relational table IN might work faster than EXISTS and JOIN in subquery.	In case of EXISTS we know that it will return either TRUE or FALSE on the basis of the condition specified by the user. So, for a small table entry in the subquery EXISTS work more efficient than IN.	It is similar to EXISTS operator. If the subquery table has relatively lesser data then execution will be more efficient as compared to IN.