How can I check if column does not have any values when left joint is used?

I have two tables:

family:

| Position | Age |
| Dad      | 41  |
| Mom      | 45  |
| Daughter | 17  |
| Dog      |     |

and food:

| Meal         | Position |
| Steak        | Dad      |
| Salad        | Mom      |
| Spinach Soup |          | 
| Tacos        | Dad      |

I do the following query:

 `SELECT family.Position, food.Meal ".
 "FROM family LEFT JOIN food ".
    "ON family.Position = food.Position`.

and I get the following result:

Dad      - Steak
Dad      - Tacos
Mom      - Salad
Daughter -
Dog      -

Now I want to have only those rows that does not contain any value for food (i.e. the last two rows with Daughter and Dog). How can i get these rows? Can I use where foof.Meal is null? Does开发者_Python百科 the Meal column have to have certain properties to make this query possible?

You were almost there.

All that's needed now is explicitly stating that the right side of the equation should be NULL.

SQL Statement

SELECT   family.Position
         , food.Meal 
FROM     family LEFT JOIN 
         food ON family.Position = food.Position
WHERE    food.Meal IS NULL

From the Reference Manual

If there is no matching row for the right table in the ON or USING part in a LEFT JOIN, a row with all columns set to NULL is used for the right table. You can use this fact to find rows in a table that have no counterpart in another table:

There is more than one solution.

One of it is adjusting your WHERE-condition, like...

SELECT family.Position, food.Meal 
FROM family 
LEFT JOIN food ON family.Position = food.Position
WHERE food.Meal IS NULL;

Use "where meal is null" and it works even if "meal" is "not null".

i don't know how to do it mysql but sql server

SELECT family.Position, food.Meal ". "FROM family LEFT JOIN food ". "ON family.Position != food.Position.