1

The following SQL statement: 

select * from employees e   
left join departments d on e.deptid = d.deptid   
where ( d.deptname = 'HR' or d.deptname  = 'HR & Accounts')

Produces the same results as the following inner join: 

select * from employees e  
   inner join departments d on e.deptid = d.deptid and d.deptname like '%HR%';

In what way they produce the same result.

I mean is the first query equivalent e.g. of:

  1. Select * from employees and filter using where
  2. Do left join?

What are the steps of the first query that make it the same as the inner join?

Cratylus
  • 52,998
  • 69
  • 209
  • 339
  • 1
    Yes, `WHERE` happens (logically) after `FROM` (which includes the joins). See this: [logical order of execution](http://stackoverflow.com/questions/6545664/using-case-expression-column-in-where-clause/6545685#6545685) – ypercubeᵀᴹ Feb 16 '13 at 11:46

2 Answers2

3

For rows that don't match the condition of the Outer Join, the value of any column in the joined table is NULL. The Where clause happens (conceptually) after the Joins are processed, so any condition testing those columns will be false (or, strictly speaking, NULL).

If you want to include all rows from one table, but only match rows in a second table which meet a condition, you have to add that condition to the ON clause of your Outer Join.

Also, note that because an Inner Join discards rows where there is no match, a condition in the ON clause of an Inner Join has the same effect as if it was in the Where clause.

IMSoP
  • 89,526
  • 13
  • 117
  • 169
  • So the `WHERE` happens *after* the `JOIN`? – Cratylus Feb 16 '13 at 11:43
  • Yes - I said "conceptually" because the DBMS will actually process data in whatever order is most efficient, but logically, a WHERE clause can only be tested after all the tables have been JOINed together. – IMSoP Feb 16 '13 at 11:51
1

With your second query, you filter in the join condition.

The first query left joins employees to departments using deptid. It returns all possible results to then filter using your where clause. This filtering after essentially alters the query to return results similar to an inner join query.

You could alter the first query to show you what it fails to return because of your where clause like this:

select *, CASE WHEN d.deptname = 'HR' or d.deptname  = 'HR & Accounts' THEN 1 ELSE 0 END as HR
from employees e 
left join departments d on e.deptid = d.deptid;
chris31389
  • 8,414
  • 7
  • 55
  • 66
  • So the `WHERE` happens after the `JOIN`? – Cratylus Feb 16 '13 at 11:44
  • Generally, the planner might be smart and do it first based on index's. You can use explain before a query to show you how it creates a plan of how to execute your query. In SQL server you can click the "Display estimated execution plan" button to show you the details. – chris31389 Feb 16 '13 at 11:49
  • @chris31389 Query optimization is sort of beside the point here: the SQL has a logical structure and order which is deliberately simpler than the underlying mechanics. Looking at the execution plan is a good thing to do once you've grasped the basics, though. – IMSoP Feb 16 '13 at 12:01