SQL INNER JOIN and UNION

Question

Lets say I have two tables Phone and Color

So on the Phone table in contains PhoneID,make,colorID

And on the color table it contains colorID, colorname

The two colorIDs are linked with a FK, but it is possible for a Phone to have a blank colorID

If I want to display a table that has PhoneID,make,colorname even if it hasn't got a colorID

So I have got a query like this

SELECT [PhoneID], [make], [colorID], FROM [Phone]

INNER JOIN [Color]
ON Phone.colorID = [color].[colorID];

But I want it to display all results even if it doesn't have a colorID and I want it to display the colorname instead of the colorID.

I am a bit confused on how to do this Thanks

possible duplicate of [Difference between INNER and OUTER joins](http://stackoverflow.com/questions/38549/difference-between-inner-and-outer-joins) — user2864740, Mar 16 '15 at 20:22
I would really encourage you to check [this](http://blog.codinghorror.com/a-visual-explanation-of-sql-joins/) out. It really helped me when I was figuring out joins. — Dan Hogan, Mar 16 '15 at 20:23

score 4 · Answer 1 · answered Mar 16 '15 at 20:21

Instead of using INNER JOIN you should be using a LEFT JOIN.

Left Join Definition: "The LEFT JOIN keyword returns all rows from the left table (table1), with the matching rows in the right table (table2). The result is NULL in the right side when there is no match."

score 1 · Answer 2 · answered Mar 16 '15 at 20:22

1

use left join that will

SELECT [PhoneID], [make], [colorID], FROM [Phone]
Left JOIN [Color]
ON Phone.colorID = [color].[colorID];

As far as the color name is concerned, you will have to join with color table and print colorName instead of id.

answered Mar 16 '15 at 20:22

Danyal Sandeelo

12,196
10
47
78

score 1 · Answer 3 · edited May 23 '17 at 11:46

1

You want to use a LEFT JOIN.

SELECT [PhoneID], [make], [colorID], FROM [Phone]
    LEFT JOIN [Color]
        ON Phone.colorID = [color].[colorID];

edited May 23 '17 at 11:46

Community

1
1

answered Mar 16 '15 at 20:23

Ron Smith

3,241
1
13
16

score 1 · Accepted Answer · answered Mar 16 '15 at 20:26

As suggested above, go for a Left Join to fetch required results. Also, if you need to display color name instead of colorID, check the SQL function ISNULL (assuming you are using MS SQL Server). ISNULL function will allow you to set a default value when colorID is blank

So something like:

select phoneID, make, ISNULL(colorID, 'your_default_value') FROM .....
LEFT JOIN ......

SQL INNER JOIN and UNION

4 Answers4