Join tables even if don't exist on other table

2020-04-01 mysql sql

I have 2 tables and want to join but if row is not existing in other table results are not shown.

+-------------------+   +-------------------+   
|      TABLE 1|     |   |     Table 2       |
+-------------------+   +-------------------+   
|deviceID|  xxx     |   |deviceID|    yyy   |   
|-------------------|   |-------------------|   
| 1    |  Service 1 |   | 1    |   Asset 1  | 
| 2    |  Service 2 |   | 2    |   Asset 2  |   
| 3    |  Service 3 |   |      |   
+-------------------+   +-------------------+

SQL:

SELECT L.xxx, A.yyy from TABLE1 L JOIN TABLE2 A on L.deviceID=A.deviceID

The results are shown are deviceID:1,2 and 3 is not showing, I want to show it even if it doesn't exist on table2

I have tried LEFT JOIN and RIGHT JOIN but is not working

Answers

Did you try a simple left join?

SELECT L.xxx, A.yyy
FROM TABLE1 L LEFT JOIN
     TABLE2 A 
     ON L.deviceID = A.deviceID;

This should do what you want.

As this example shows, LEFT JOIN works just fine. For you.

So that doesn't seems to be the problem. cam you provide an dbfoddle example whre it doesn't work

Schema (MySQL v8.0)

CREATE TABLE Table2 (
  `deviceID` INTEGER,
  `yyy` VARCHAR(7)
);

INSERT INTO Table2
  (`deviceID`, `yyy`)
VALUES
  ('1', 'Asset 1'),
  ('2', 'Asset 2');

CREATE TABLE Table1 (
  `deviceID` INTEGER,
  `xxx` VARCHAR(9)
);

INSERT INTO Table1
  (`deviceID`, `xxx`)
VALUES
  ('1', 'Service 1'),
  ('2', 'Service 2'),
  ('3', 'Service 3');

Query #1

SELECT
t1.deviceID
,t1.xxx
,t2.yyy
FROM
Table1 t1 LEFT JOIN
Table2 t2 ON t1.deviceID = t2.deviceID;

| deviceID | xxx       | yyy     |
| -------- | --------- | ------- |
| 1        | Service 1 | Asset 1 |
| 2        | Service 2 | Asset 2 |
| 3        | Service 3 |         |

Query #2

SELECT

t1.xxx
,t2.yyy
FROM
Table1 t1 LEFT JOIN
Table2 t2 ON t1.deviceID = t2.deviceID;

| xxx       | yyy     |
| --------- | ------- |
| Service 1 | Asset 1 |
| Service 2 | Asset 2 |
| Service 3 |         |

View on DB Fiddle

Related