0

I have been trying to figure out how to select data related to one id between to tables without limit it to the joined table. I tried using UNION, Inner join, JOIN, but it limit me to show records that are only in both tables. By example: 

Table 1 (users)

id | name | register

1  | John | 2014-03-01

2  | Kate | 2014-03-02

etc..

Table 2 (birthdays by example)

id | user | birthday

1  |  1   | 1989-09-09

Note that kate dont have a record on the birthdays table, if i do:

SELECT U.id, name, register, B.birthday FROM users as U INNER JOIN birthday as B ON B.user = U.id

it will only shows JOHN data, i would like to select all my users and if the record do not exist on the joined table, still be able to select all my users, sort of:

id | name | register   | birthday 

1  | John | 2014-03-01 | 1989-09-09

2  | kate | 2014-03-02 | null or ''

3

4

etc.

Sorry if its a stupid question but i dont find the light on this one. I would appreciate the help.

Regards

codemania
  • 1,098
  • 1
  • 9
  • 26
Aramil
  • 411
  • 1
  • 5
  • 17

2 Answers2

2

You need a LEFT OUTER JOIN instead of the plain JOIN (also known as INNER JOIN), like this:

SELECT U.id, name, register, B.birthday 
FROM users as U 
LEFT JOIN birthday as B 
ON B.user = U.id

A LEFT JOIN between users and birthday tables will contain all records of the "left" table (users), even if the join-condition does not find any matching record in the "right" table (birthday).

This excellent article on The Code Project will help you a lot: Visual Representation of SQL Joins.

Summary of all JOIN types:

enter image description here

Note: Mysql does not support FULL OUTER JOIN but it can be emulated. Useful articles:

Community
  • 1
  • 1
Aziz Shaikh
  • 16,245
  • 11
  • 62
  • 79
  • @Aziz Shaikh: I appreciate your effort to share this effective graphical diagram to understand different joins but as per my understanding mysql does not support FULL OUTER JOIN. Please correct me if I am wrong. – Zafar Malik Mar 07 '14 at 06:25
  • @ZafarMalik you are absolutely right, I have added that to my answer above. Thanks for the pointer. The image was to give a general idea about the concept of JOINs. – Aziz Shaikh Mar 07 '14 at 06:39
0

Use left outer join instead of inner join..

SELECT U.id, name, register, B.birthday 
FROM users as U left join birthday as B ON B.user = U.id
G one
  • 2,679
  • 2
  • 14
  • 18