CODEWITHSUNDEEP
sqlsql-serverjoinleft-joininner-join
user1017882
user1017882

Reputation:

Struggling to interpret this SQL join syntax

Two pretty similar queries:

Query #1:

SELECT *
FROM employee e
LEFT JOIN employee_payments ep
INNER JOIN payments p ON ep.payment_id = p.id
    ON ep.employee_id = e.id

Query #2:

SELECT *
FROM employee e
LEFT JOIN employee_payments ep ON ep.employee_id = e.id
INNER JOIN payments p ON ep.payment_id = p.id

But obviously crucially different syntax.

The way I learn these new syntax concepts best are to interpret them as plain English. So how could you describe what these are selecting?

I would expect that they'd produce the same results, but it feels to me like the LEFT JOIN in the second query acts as an INNER JOIN somehow - since a fraction of my results set are returned (i.e. the employees with payments).

If the first query 'says' "give me all employees, along with any available employee_payments (that have already been joined with their payment record)"- what does the second query say?

Upvotes: 0

Views: 117

Answers (2)

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239684

In order to understand the logical order1 in which joins happen, you need to look at the ON clauses. For each ON clause that you encounter, you pair it with the closest previous JOIN clause that hasn't already been processed. This means that you first query is:

INNER JOIN ep to p (producing, say, ep')
LEFT JOIN e to ep'

And your second query is:

LEFT JOIN e to ep (producing, say, e')
INNER JOIN e' to p

Since the conditions of the INNER JOIN rely upon columns present in ep, this is why the different join orders matter here.

1The logical join order determines the final shape of the result set. SQL Server is free to perform joins in any order it sees fit, but it must produce results consistent with the logical join order.

Upvotes: 1

user743382
user743382

Reputation:

If the first query 'says' "give me all employees, along with any available employee_payments (that have already been joined with their payment record)"- what does the second query say?

I suppose you might put it as "Take all employees along with any available employee_payments. Join this with the payment records."

The "Join this with the payment records" is what filters out employees that don't have any associated employee_payments records: the attempt to join with the payment records will fail.

but it feels to me like the LEFT JOIN in the second query acts as an INNER JOIN somehow

It's not the LEFT JOIN that's doing the filtering, but it does indeed give the exact same result as if the LEFT JOIN had been an INNER JOIN.

Upvotes: 2

Related Questions