Skip to content
loading...

One minute tips : Less noise in SQL joins

github logo ・1 min read  

Published at: https://codefromdude.com/posts/one-min-tip-less-noice-in-sql-joins

Assume that we need to join two tables based on some condition. For example,

SELECT users.id, accounts.name from users, accounts where users.account_id = 1 AND users.account_id = accounts.id
SELECT users.id, accounts.name from users INNER JOIN accounts on accounts.id = users.account_id where users.account_id = 1

Both queries do the same. we are saving 3 words in the first query (this might help you to save 9 secs for every join you make).

Check the query plan for both of these queries, It will be same. I’ve checked this with MySql and Pgsql. I hope this will work in most of the DBMS systems. Let me know if you have any tricks like this.

twitter logo DISCUSS (4)
markdown guide
 

What you call less noise I call more information.

The first SQL Query is using what's known as an implicit join (A.K.A comma join).
The join clause is replaced by a comma, and the join criteria is specified in the where clause.

The second SQL Query is using what's known as an explicit join.
The join criteria has it's own dedicated clause (the ON clause).

BTW, these queries are not the same: The first is an inner join and the second is a left outer join.

Implicit joins requires a little less typing, but that's probably the only benefit over explicit joins.

Explicit joins are a part of the ANSI standard for almost 30 years now, and with good reasons too:

  1. Explicit joins provides a clear distinction between join conditions and filter conditions.

  2. Inner joins can easily be replaced to an outer (left, right or full) join simply by replacing (or adding) a keyword - implicit outer joins are no longer supported at least on one major rdbms - SQL Server. (they where deprecated in 2008 version, which ended it's extended support in July 2019).

  3. Explicit joins are far less error prone - since the join condition is in the on clause, the compiler will not let you run a join query without specifying a join condition.

  4. Explicit joins are far more readable and maintainable.

  5. With multiple joins in a query, explicit joins provides a very easy way to distinguish which condition belongs to which join.

 

WOW!. Thanks for pointing out the mistake. Thanks for the clarification. I would go with implicit join if I just want to execute some queries that are used only once, let's say in the console.

 
 

My OCD kicked in with the single line SQL Queries.

SELECT users.id, accounts.name 
from users, accounts 
where users.account_id = 1 
    AND users.account_id = accounts.id
SELECT users.id, accounts.name
from users 
INNER JOIN accounts on accounts.id = users.account_id 
where users.account_id = 1
Classic DEV Post from Aug 30 '19

SELECT Post FROM Stack Overflow Questions WHERE Topic = "git" ORDER BY Votes DESC;

Gokuldroid profile image