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 Solution to the error “Visual Studio Code can’t be opened because Apple cannot check it for malicious software” #2 How to add Read More Read Less Button using JavaScript? #3 How to check if radio button is checked or not using JavaScript? #4 Solution to “TypeError: ‘x’ is not iterable” in Angular 9 #5 PHP Login System using PDO Part 1: Create User Registration Page #6 How to uninstall Cocoapods from the Mac OS?

Recently Posted

#Apr 8 JSON.stringify() in JavaScript #Apr 7 Middleware in NextJS #Jan 17 4 advanced ways to search Colleague #Jan 16 Colleague UI Basics: The Search Area #Jan 16 Colleague UI Basics: The Context Area #Jan 16 Colleague UI Basics: Accessing the user interface
You might also like these
Composing and Extracting Components in ReactReactFull and Partial CUBE in Postgres with ExamplesPostgresFlash of Invisible Text and Mitt Romney Web Font ProblemCSSHow to link/add CSS file to HTML Document?CSSSorting Object Arrays in JavaScriptJavaScriptHow to check if the page is single post page in WordPress?WordPress