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: 

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