How to select all rows but join information under condition

Question

Simplified I have two tables NAMES (with columns ID and NAME) and ADRESSES (with columns COUNTRY and CITY and NAME_ID).

Example for NAMES:

1 - Hans
2 - Mark
3 - Joseph

Example for ADRESSES:

Denmark - Kopenhagen - 1
Germany - Berlin - 3

I need to select all Names which either have no adress at all OR with their CITY but only when their country is... Denmark.

SELECT
NA.NAME AS NAME,
AD.CITY AS CITY
FROM NAMES AS NA
LEFT JOIN ADRESSES AS AD ON AD.NAME_ID = NA.ID

now when I add something like

WHERE AD.COUNTRY="Denmark"

or

WHERE (AD.COUNTRY="Denmark" OR AD.COUNTRY=NULL)

I still only get a list of Names with Cities in Denmark but not all other Names which have no Adress/City at all.

When I remove the condition of course I get all Names and existing Cities but even in all other countries.

The desired result would be:

Hans - Kopenhagen
Mark - NULL

Try to use WHERE (AD.CITY="Denmark" OR AD.CITY IS NULL) take a look at this link https://stackoverflow.com/questions/21214231/difference-between-columnname-null-and-columnname-is-null-in-sql-server — Rick Bronger, Sep 23 '19 at 14:34
Which [DBMS](https://en.wikipedia.org/wiki/DBMS) product are you using? "SQL" is just a query language, not the name of a specific database product (and `city = "Denmark"` is invalid standard SQL). Please add a [tag](https://stackoverflow.com/help/tagging) for the database product you are using — , Sep 23 '19 at 14:35
Move AD.COUNTRY="Denmark" predicate to ON from WHERE to keep LEFT JOIN left. Also, `AD.CITY=NULL` is never true in SQL, use `AD.CITY IS NULL` — Serg, Sep 23 '19 at 14:48

Ankit Bajpai · Accepted Answer · 2019-09-23T19:35:33.673

1

I guess you mean WHERE AD.COUNTRY='Denmark' not WHERE AD.CITY='Denmark'. Also, Using WHERE clause with LEFT JOIN makes it like INNER JOIN. So shift your condition to LEFT JOIN clause -

SELECT NA.NAME AS NAME,
       AD.CITY AS CITY
FROM NAMES AS NA
LEFT JOIN ADRESSES AS AD ON AD.NAME_ID = NA.ID
WHERE AD.COUNTRY='Denmark' OR AD.COUNTRY IS NULL

edited Sep 23 '19 at 19:35

answered Sep 23 '19 at 14:48

Ankit Bajpai

13,128
4
25
40

1

frist you cant compare something to null, you need `IS NULL` second you dont need the `OR` condition. the NULL is assigned after the `LEFT JOIN` – Juan Carlos Oropeza Sep 23 '19 at 14:58
@JuanCarlosOropeza, Agrees on first one and corrected the same. I just didn't notice it. For 2nd, Are you really sure, We don't need `OR` condition. – Ankit Bajpai Sep 23 '19 at 15:25
Very sure. Unless you have null on the original table you dont have null after the left join is done. So in that case you can use that check on the `WHERE` – Juan Carlos Oropeza Sep 23 '19 at 15:29

score 0 · Answer 2 · answered Sep 23 '19 at 15:34

0

I need to select all Names which either have no adress at all OR with their CITY but only when their country is... Denmark.

I believe you want:

SELECT NA.NAME AS NAME, AD.CITY AS CITY
FROM NAMES NA LEFT JOIN 
     ADRESSES AD
     ON AD.NAME_ID = NA.ID
WHERE AD.COUNTRY = 'DENMARK' OR AD.COUNTRY IS NULL;

This should not return names in other countries, such as Germany.

answered Sep 23 '19 at 15:34

Gordon Linoff

1,242,037
58
646
786

OP sample output show Mark from Germany but with NULL in the address. – Juan Carlos Oropeza Sep 23 '19 at 15:36
@JuanCarlosOropeza . . . The desired results filter out Joseph, which directly contradicts at least three other places of explanation. I think the specified results are not correct. In any case, the other answers do not filter rows at all so they are not correct. – Gordon Linoff Sep 23 '19 at 16:11
You are right, not all answer solve everything on the question. Looks like OP is German so maybe English isn't his strong language, but seem to have found this answer. – Juan Carlos Oropeza Sep 23 '19 at 16:13
@JuanCarlosOropeza, Joseph is from Germany not Mark. I guess you are getting confused between them. – Ankit Bajpai Sep 23 '19 at 18:48

How to select all rows but join information under condition

2 Answers2