I have two tables with one primary key present on both of them lets call them LEFT table and RIGHT table.I want to make a join such that I get all the rows of left table only ,i.e. if ROW from RIGHT table matches the JOIN condition it should get horizontally appended to the specific row.Else all NA in second half of the row,so that number of rows in the join can only by equal to number of rows in LEFT table
Mysql – Which join to use
join;MySQL
Related Solutions
All INNER JOINs and UNIONs maybe
PROPOSED QUERY
select id t1_id,NULL t2_id,NULL t3_id,NULL t4_id from t1
union select t1.id,t2.id,NULL,NULL from t1
inner join t2 using (id)
union select t1.id,t2.id,t3.id,NULL from t1
inner join t2 using (id)
inner join t3 using (id)
union select t1.id,t2.id,t3.id,t4.id from t1
inner join t2 using (id)
inner join t3 using (id)
inner join t4 using (id)
;
SAMPLE DATA
use test
drop table if exists t1;
drop table if exists t2;
drop table if exists t3;
drop table if exists t4;
create table t1
(
id int not null,
name varchar(20),
primary key (id)
);
insert into t1 values (1,'name1');
create table t2 like t1; insert into t2 values (1,'name2');
create table t3 like t1; insert into t3 values (1,'name3');
create table t4 like t1; insert into t4 values (1,'name4');
PROPOSED QUERY EXECUTED
mysql> select id t1_id,NULL t2_id,NULL t3_id,NULL t4_id from t1
-> union select t1.id,t2.id,NULL,NULL from t1
-> inner join t2 using (id)
-> union select t1.id,t2.id,t3.id,NULL from t1
-> inner join t2 using (id)
-> inner join t3 using (id)
-> union select t1.id,t2.id,t3.id,t4.id from t1
-> inner join t2 using (id)
-> inner join t3 using (id)
-> inner join t4 using (id)
-> ;
+-------+-------+-------+-------+
| t1_id | t2_id | t3_id | t4_id |
+-------+-------+-------+-------+
| 1 | NULL | NULL | NULL |
| 1 | 1 | NULL | NULL |
| 1 | 1 | 1 | NULL |
| 1 | 1 | 1 | 1 |
+-------+-------+-------+-------+
4 rows in set (0.00 sec)
mysql>
All the columns have id as a common name. If you want, I can adjust my answer with the id names
First, join TABLE_2
and TABLE_3
using an inner join and additionally filtering on Value_A = 'a'
:
SELECT
t2.ID_1,
t3.ID_A,
t3.Value_A
FROM
TABLE_2 t2
INNER JOIN TABLE_3 t3 ON t2.ID_A = t3.ID_A
WHERE
t3.Value_A = 'a'
This will give you the following result set:
ID_1 ID_A Value_A
---- ---- -------
1 b a
4 d a
Now use the above as a derived table and join it, using an outer join this time, to TABLE_1
, additionally filtering the results on Value_1 = 11
:
SELECT
t1 .ID_1,
t23.ID_A,
t1 .Value_1,
t23.Value_A
FROM
TABLE_1 t1
LEFT JOIN
(
SELECT
t2.ID_1,
t3.ID_A,
t3.Value_A
FROM
TABLE_2 t2
INNER JOIN TABLE_3 t3 ON t2.ID_A = t3.ID_A
WHERE
t3.Value_A = 'a'
) t23 ON t1.ID_1 = t23.ID_1
WHERE
t1.Value_1 = 11
;
That will give you the output you want:
ID_1 ID_A Value_1 Value_A
---- ---- ------- -------
1 b 11 a
3 NULL 11 NULL
However, nesting a query is not the only way to solve your problem – you can also use a nested join, which is much more concise:
SELECT
t1.ID_1,
t3.ID_A,
t1.Value_1,
t3.Value_A
FROM
TABLE_1 t1
LEFT JOIN
TABLE_2 t2
INNER JOIN TABLE_3 t3 ON t2.ID_A = t3.ID_A AND t3.Value_A = 'a'
ON t1.ID_1 = t2.ID_1
WHERE
t1.Value_1 = 11
;
The last query implements exactly the same logic as the previous query: first, tables TABLE_2
and TABLE_3
are joined and the result is filtered, then it is joined to TABLE_1
and the final set is filtered again. Some people also add brackets around a nested join:
FROM
TABLE_1 t1
LEFT JOIN
(
TABLE_2 t2
INNER JOIN TABLE_3 t3 ON t2.ID_A = t3.ID_A AND t3.Value_A = 'a'
)
ON t1.ID_1 = t2.ID_1
to make it clearer (perhaps both for themselves and for future maintainers) that the nested join takes place before the outer-level one, logically, although the syntax is unambiguous enough without them.
Nevertheless, many people find it confusing even with brackets, and if you find yourself struggling with it as well, there is another option – right outer join:
SELECT
t1.ID_1,
t3.ID_A,
t1.Value_1,
t3.Value_A
FROM
TABLE_2 t2
INNER JOIN TABLE_3 t3 ON t2.ID_A = t3.ID_A AND t3.Value_A = 'a'
RIGHT JOIN TABLE_1 t1 ON t1.ID_1 = t2.ID_1
WHERE
t1.Value_1 = 11
;
Again, the logical sequence of events specified by the FROM clause repeats that of the derived table solution: a join between TABLE_2
and TABLE_3
, filtered, is followed by a join with TABLE_1
. The different syntax does not alter the outcome and the results produced still match your requirements.
Related Question
- Mysql – Which join to use
- Sql-server – comparing left join and outer apply doing the same thing
- Postgresql – Which index shall I create in Postgres to speedup join of two big (several million rows) tables connected one-to-many
- T-sql – Join table exactly once
- SQL squash rows returned when joining several tables
Best Answer
You are describing a
LEFT JOIN
. e.g.This will list all values in table1.column1 on the left, and any values in table2.column2 that match on the right. If they don't match, the field in the right column will be null.