MySQL SELECT JOIN with empty row returns

Question

I have the following MySQL tables:

[users]

| id | name    |
|----|---------|
| 1  | John    |
| 2  | Anna    |
| 3  | Peter   |

[times]

| user_ID | date       | time   |
|---------|------------|--------|
| 1       | 2020-03-20 | 07:00  |
| 1       | 2020-03-21 | 08:00  |
| 3       | 2020-03-22 | 09:00  |

my query look like:

SELECT name, date, time 
FROM users 
INNER JOIN times ON times.user_ID = users.id 
WHERE date = '2020-03-22';

what i get is:

| name    | date       | time   |
|---------|------------|--------|
| Peter   | 2020-03-22 | 09:00  |

what i want is:

| name    | date       | time   |
|---------|------------|--------|
| John    |            |        |
| Anna    |            |        |
| Peter   | 2020-03-22 | 09:00  |

is there a way to join non existent lines (not fields!) in the times table with the users table?

Consider storing date and time as a single entity

Strawberry
– Strawberry

2020-03-20 21:37:31 +00:00
Commented Mar 20, 2020 at 21:37 — Strawberry
– Strawberry, Commented Mar 20, 2020 at 21:37
I would use UNION instead of an Inner Join

Rachel McGuigan
– Rachel McGuigan

2020-03-20 23:06:39 +00:00
Commented Mar 20, 2020 at 23:06 — Rachel McGuigan
– Rachel McGuigan, Commented Mar 20, 2020 at 23:06

Barmar · Accepted Answer · 2020-03-20 21:06:31Z

1

Use LEFT JOIN. And then you need to put the restrictions on the second table into the ON clause.

SELECT name, date, time 
FROM users 
LEFT JOIN times ON times.user_ID = users.id AND date = '2020-03-22';

answered Mar 20, 2020 at 21:06

Barmar

789k57 gold badges555 silver badges669 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Jens · Accepted Answer · 2020-03-20 21:06:42Z

1

use left join and move where clause to on:

SELECT name, date, time 
FROM users 
left JOIN times ON times.user_ID = users.id and date = '2020-03-22';

answered Mar 20, 2020 at 21:06

Jens

69.6k15 gold badges107 silver badges133 bronze badges

2 Comments

3x3cut0r Over a year ago

thank you! that gave me the right idea of how this is working. i already supposed about using left and right joins but i did it wrong with the where clause. now i'm into it thanks! can i also replace null fields with some default values? (like '00:00:00')

Jens Over a year ago

@3x3cut0r Yes you can if the ifnull function

Collectives™ on Stack Overflow

MySQL SELECT JOIN with empty row returns

2 Answers 2

Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related