What are the advantages & disadvantages of a join or a subquery?

A subquery is a query within another query. A subquery also might contain another subquery. These are said to be "nested" subqueries. A join is when two or more tables are referenced in the "From" clause of the query. Different database engines may optimise subqueries and joins differently. For example, DB2's optimizer will transform a subquery to a join if certain criteria are met. In some instances, a join yields better performance than a subquery.

A subquery is a "SELECT" statement within the "WHERE" or "HAVING" clause of an outer "INSERT," "UPDATE," "MERGE" or "DELETE" statement. The subquery also is called the "inner query" or "inner select." The subquery may have a "FROM" clause with one or more tables and may optionally have a "WHERE," "GROUP BY" or "HAVING" clause. It is always enclosed in parenthesis.

Subqueries are advantageous because they structure the query to isolate each part of the statement, perform the same operation that would ordinarily require complex joins and unions and are easier to read. Subqueries even were the basis for the name "Structured Query Language" (SQL) because of their easily readable structure. A disadvantage is that you cannot modify a table and select from the same table within a subquery in the same SQL statement. Subqueries also can take longer to execute than a join because of how the database optimizer processes them.

Most subqueries can be rewritten as joins, and most joins can be rewritten as subqueries. A join defines two or more tables by a related column. Tables usually are joined on primary and foreign keys. For example, an employee table might have a primary key of an employee id column, while a timesheet table also has an employee id column that is a foreign key to the employee table. The SQL join can be written as "WHERE employee.empid = timesheet.empid" or "FROM employee JOIN timesheet ON (employee.empid = timesheet.empid)."

The main advantage of a join is that it executes faster. The performance increase might not be noticeable by the end user. However, because the columns are specifically named and indexed and optimised by the database engine, the retrieval time almost always will be faster than that of a subquery. There are also inner and outer joins, left and right joins, full joins and cross joins. A disadvantage of using joins is that they are not as easy to read as subqueries. Another disadvantage is that it can be confusing as to which join is the appropriate type of join to use to yield the correct desired result set.