Join WHERE clause - table has row with defined value, or no entry

Question

Can anyone help me figure out the correct WHERE clause for the following scenario:

select A.name from tableA A, tableB B where A.id = B.id and ( B.field = 5 OR B.hasNoRowForJoinedID )

I feel like I'm missing something really obvious here in how to accomplish this, but I can't for the life of me put my finger on it.

Sample data and desired results would help clarify the question. You should learn what the difference is between Brian's and Giorgi's answers, so you can understand why the question is unclear. — Gordon Linoff
– Gordon Linoff, Commented Dec 18, 2015 at 11:39

Brian S · Accepted Answer · 2015-12-18 11:39:53Z

You are using an outdated SQL Syntax. To perform the LEFT OUTER JOIN based your your request above, you can do the following:

SELECT A.name FROM A LEFT OUTER JOIN B ON A.id = B.id WHERE (B.field = 5 OR B.field IS NULL)

Giorgi Nakeuri · Accepted Answer · 2015-12-18 11:35:55Z

1

Use proper join syntax and not the outdated ones:

select A.name from tableA A left join tableB B on A.id = B.id and B.field = 5

answered Dec 18, 2015 at 11:35

35.9k8 gold badges50 silver badges78 bronze badges

3 Comments

Would using that approach give me the entries from A which have no corresponding entry in B?

@Strongo, yes because of left joining