What is Subquery?

A Subquery or Inner query or Nested query is a query within an SQL query and embedded within the WHERE clause. A Subquery is a SELECT statement that is embedded in a clause of another SQL statement.

They can be very useful for selecting rows from a table with a condition that depends on the data in the same or another table. A Subquery is used to return data that will be used in the main query as a condition to further restrict the data to be retrieved. The subquery can be placed in the following SQL clauses they are WHERE clause, HAVING clause, and FROM clause.

Advantages Of Subquery:

• Subqueries divide the complex query into isolated parts so that a complex query can be broken down into a series of logical steps.
• It is easy to understand and code maintenance is also at ease.
• Subqueries allow you to use the results of another query in the outer query.
• In some cases, subqueries can replace complex joins and unions. 

Disadvantages of Subquery:

• The optimizer is more mature for MYSQL for joins than for subqueries, so in many cases a statement that uses a subquery can be executed more efficiently if you rewrite it as join.
• We cannot modify a table and select from the same table within a subquery in the same SQL statement.

The difference between SQL JOIN and subquery is that JOIN combines records of two or more tables whereas Subquery is a query nested in another query.

SQL JOIN and Subquery are used to combine data from different tables simplifying complex queries into a single statement.

Here we will discuss SQL JOIN vs Subquery in detail, and understand the difference between JOIN and Subquery with examples.