Ads by ProfitSence
Close

Join vs SubQuery

Last Updated on Wednesday 5th Oct 2022

SubQuery vs Join

  • Difference between subquery and join
  • 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 subquery can be nested inside a SELECT, INSERT, UPDATE, or DELETE statement or inside another subquery.

join vs subquery

  • A JOIN clause is used to combine rows from two or more tables, based on a related column between them.
  • It connects two or more tables and selects data from them into a single result set.

sql subquery

  • Subqueries and JOINs can both be used in a complex query to select data from multiple tables, but they do so in different ways.
  • JOINs are faster than sub-queries and it is very rare for a sub-query to be faster.

subqueries vs joins

A subquery is a SELECT that appears as part of another SQL statement.

  • In the WHERE clause of another SELECT statement - this is called a nested subquery.
  • In the FROM clause of another SELECT statement - this is called an inline view.
  • The result of the subquery is treated as though it were another table; you can even give the subquery a table alias.
  • As an expression in a SELECT, VALUES, or SET clause.
			
					SELECT fname, lname, uname
  FROM ub
 WHERE id = (SELECT id
               FROM ed
              WHERE uid = 1);

			
	

Sub-queries in Select-Fields

			
					SELECT AB, (SELECT BC FROM ALPs WHERE AB = LF) AS bar FROM foo

			
	

Sub-queries in the Where-statement

			
					SELECT AB FROM foo WHERE AB = (SELECT BC FROM ALPs WHERE AB = me)

			
	

Sub-queries in the Join-statement

			
					SELECT AB, BC 
  FROM foo 
    LEFT JOIN (
      SELECT MIN(BC), me FROM ALPs GROUP BY AB
    ) ON AB = me

			
	

Sub-queries are no replacement for a JOIN and you should not use them like this.

Subquery vs Join

When the query is only returning column values from a single table, you can use a subquery in the WHERE clause to help limit the result set.

			
					SELECT first_name, last_name
  FROM staff
 WHERE id IN (SELECT customer_id FROM rental);

			
	

When the query returns column values from multiple tables, you will have to use a JOIN so that all of the table data is available in the main query.

			
					SELECT first_name, last_name, rental_date
  FROM staff c JOIN rental r
                 ON c.id = r.customer_id;