Do Not Put Filtering Conditions In the "WHERE" Clause in Some Scenarios

As long as you click into this article, I'm pretty sure you should understand Sql. You must also understand that we should put our conditions in the WHERE clause in a SELECT query. However, let me ask you a question and see if you can answer it immediately.

What will happen if we put a filter condition with LEFT JOIN … ON … clause?

SELECT *
FROM Employee e LEFT JOIN Department d
  ON e.dept_id = d.id
  AND e.name = 'Chris'

If you are unsure about the behaviour of the above query or think it is equivalent to the following one, please read my article and I'll tell you why they are different.

SELECT *
FROM Employee e LEFT JOIN Department d
  ON e.dept_id = d.id
WHERE e.name = 'Chris'

1. Verify the Results

For demonstration purposes, I have created two tables with simple dummy data as follows.

The Employee Table

The Department Table

Now, let's run the first query in the introduction. For your reference, I'll put the query here again.

SELECT *
FROM Employee e LEFT JOIN Department d
  ON e.dept_id = d.id
  AND e.name = 'Chris'

That was the answer. Were you right?

Tags: Artificial Intelligence Data Science Programming Sql Technology

Comment