One of the greatest challenges in writing queries is deciding which to use, whether to use joins or sub-queries.

The truth is that they vary, there are situations that subquery is best to use and there are some situations that join is the best option.

Subqueries are used in complex SQL queries. Usually, there is a main outer query and one or more subqueries nested within the outer query.

Subqueries can be simple or correlated. Simple subqueries do not rely on the columns in the outer query, whereas correlated subqueries refer to data from the outer query.

The JOIN clause does not contain additional queries. It connects two or more tables and selects data from them into a single result set. It is most frequently used to join tables with primary and foreign keys.

Subqueries & Joins can both be used in a complex query to select data from multiple tables, but they do so in different ways. Sometimes you have a choice of either, but there are cases in which a subquery is the only real option. 

Subqueries are advantageous over joins when you have to calculate an aggregate value and use it in the outer query for comparison.

Example:

Provide the employee numbers and the start date of all employees with start date equal to the earliest data.

Solution:

SELECT emp_no, start_date

FROM employees

WHERE start_date =(select min(start_date) from department);

This problem cannot be easily solved with a JOIN because you have aggregate function in the WHERE clause which is not allowed when using a WHERE clause.

Joins are advantageous over subqueries if the SELECT list in a query contains columns from more than one table.

Example:

Get the employee numbers, last name and jobs for all employees who entered their projects on January 20,2022

Solution:

SELECT employee.employee_no, emp_lname, jobs from employee.contractor

WHERE employee.emp_no = contractor.emp_no and enter_date = ‘01.20.2022’

Note that the JOIN clause does not contain additional queries. It contains two or more tables and select data from them into a single result set.