Yogesh Chauhan's Blog

A Quick Comparison of JOIN and Subquery in SQL

in SQL/MySQL on January 6, 2020

A subquery is simply query inside a query. Nested. While JOIN is like a merger of two tables to form a new table virtually. It combines data from two different tables or the table itself(self-join).

We can get many different results from using Subquery and JOIN which, many times, can't be done by simple SQL query. But most of the subqueries can be rewritten using JOIN and vice versa. Let's take a look at the examples.

Let's take a look at the examples to understand this better.

//JOIN example

SELECT invoice_number, invoice_date, invoice_total FROM invoices JOIN sellers
ON invoices.seller_id = sellers.seller_id
WHERE seller_state='FL'
ORDER BY invoice_date;

The query above is fetching the number, date and total for each invoices and joins with sellers on sellers_id. Also, we have a condition in WHERE which will limit the result set to Florida sellers only.

Now take a look the following query.

//Subquery example

SELECT invoice_number, invoice_date, invoice_total FROM invoices
WHERE seller_id IN
(SELECT seller_id FROM sellers WHERE seller_state= 'FL')
ORDER BY invoice_date;

In the first query I have used JOIN to combine the sellers and invoices data and then I am checking for the state using WHERE condition.

In the second query, I have added a subquery in which it will only return the seller_id of Florida sellers. So, in that query I am fetching the data for only Florida sellers from invoices using WHERE condition.

So, what should we use?

I think we should use the query based on the complexity and relationship between the tables as well. But that's my opinion.

JOIN is more helpful when there is a relationship between tables like the query above. They both have seller_id column in common. Subquery is helpful whether there is a relation or not (ad hoc relationship we can say).

For complex queries subqueries are easier to use and divide the query in small parts and analyze from human eyes (more readable, more understandable). We don't always find relationships between all tables in complex queries so using subqueries might be a good idea. 

Advantages of JOIN

  • It included columns in result set from both tables.
  • More helpful/intuitive when there is a relationship between tables.

Advantages of Subquery

  • We can pass aggregate value using subquery
  • More helpful/intuitive when there is no relationship or more like ad hoc relationship
  • It makes complex and lengthy queries easier to write, more readable and understandable

Most Read

#1 How to check if radio button is checked or not using JavaScript? #2 Solution to “TypeError: ‘x’ is not iterable” in Angular 9 #3 How to add Read More Read Less Button using JavaScript? #4 How to uninstall Cocoapods from the Mac OS? #5 PHP Login System using PDO Part 1: Create User Registration Page #6 How to Use SQL MAX() Function with Dates?

Recently Posted

#Aug 15 Is PHP still good for back-end programming? #Aug 10 How to create a multisite network in WordPress? #Aug 3 How to create a circle that follows a cursor using JavaScript and CSS? #Aug 3 How to make a curtain slider using jQuery and CSS? #Aug 2 How to progressively load images and add a blurry placeholder? #Aug 1 How to create a placeholder loader (throbber) using CSS?
You might also like these
How to create a Bootstrap style accordion using CSS and JavaScript?CSSSubquery in PostgresPostgresSteps to Secure a VPNMiscellaneousHow to check if a link has http or https in it in JavaScript?JavaScriptHow to get query string values in JavaScript? (URL Manipulation using URLSearchParams)JavaScriptHow to apply style to a specific child element using CSS?CSS