CODEWITHSUNDEEP
sqljoinintersystems-cache
user736893
user736893

Reputation:

I cannot get this LEFT JOIN to work (I don't understand joins)

These queries both get results:

SELECT * FROM Table1 WHERE Criteria = '5'
SELECT * FROM Table1 WHERE Criteria = '3'

This query gets results:

SELECT *
FROM Table1 p, Table2 m
WHERE p.UID = m.ID
AND Criteria = '5'

This query does not:

SELECT *
FROM Table1 p, Table2 m
WHERE p.UID = m.ID
AND Criteria = '3'

I am trying to convert these to a proper join which returns results even if there are no records in the right table.

I have tried the following

SELECT *
FROM Table1 p LEFT JOIN Table2 m ON p.UID = m.ID
WHERE p.Criteria = '3'
AND m.OtherCriteria = 'Moron'
--0 results

My limited understanding was that LEFT join is what I needed. I want data from the left table even if there is no data in the right table that matches. Since this didn't work I also tried right join, left outer join, right outer join and full join. None returned results.

What am I missing?

Upvotes: 0

Views: 2801

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1271003

This is too long for a comment. Your query:

SELECT *
FROM Table1 p LEFT JOIN
     Table2 m
     ON p.UID = m.ID AND p.Criteria = '3';

Should be returning a row for all rows in table1. If there is no match, then the values will be NULL for table2. This is easily demonstrated: Here is a MySQL example on SQL Fiddle. Because this is standard behavior, it should work on almost any database.

Note that this query is quite different from this one:

SELECT *
FROM Table1 p LEFT JOIN
     Table2 m
     ON p.UID = m.ID 
WHERE p.Criteria = '3';

This query returns no rows, because no rows match the WHERE clause. The filtering happens (conceptually) after the LEFT JOIN.

I changed the code in the SQL Fiddle slightly, so that query is:

select *
from (select 5 as criteria, 1 as id union all
      select 6, 1 union all
      select 7, 2
     ) table1 left join
     (select 1 as id, 'x' as x
     ) table2
     on table1.id = table2.id and criteria = 3;

As a note: you should always use explicit join syntax. Simple rule: Never use commas in the FROM clause.

If your database is returning no rows, then it is behaving in a non-standard manner or your interface has decided to filter the rows for some reason.

Upvotes: 1

Related Questions