PostgreSQL/Table Joins/Left Join
Join condition specified with ON can also contain conditions that do not relate directly to the join
<source lang="sql">
postgres=# postgres=# CREATE TABLE t1 ( postgres(# num int, postgres(# name varchar(10) postgres(# ); CREATE TABLE postgres=# postgres=# insert into t1 values(1,"a"); INSERT 0 1 postgres=# insert into t1 values(2,"b"); INSERT 0 1 postgres=# insert into t1 values(3,"c"); INSERT 0 1 postgres=# postgres=# CREATE TABLE t2 ( postgres(# num int, postgres(# name varchar(10) postgres(# ); CREATE TABLE postgres=# postgres=# insert into t2 values(1,"xxx"); INSERT 0 1 postgres=# insert into t2 values(3,"yyy"); INSERT 0 1 postgres=# insert into t2 values(5,"zzz"); INSERT 0 1 postgres=# postgres=# -- The join condition specified with ON can also contain conditions that do not relate directly postgres=# -- to the join. This can prove useful for some queries but needs to be thought out carefully. postgres=# postgres=# SELECT * FROM t1 LEFT JOIN t2 ON t1.num = t2.num AND t2.name = "xxx";
num | name | num | name
+------+-----+------
1 | a | 1 | xxx 2 | b | | 3 | c | |
(3 rows) postgres=# postgres=# drop table t1; DROP TABLE postgres=# drop table t2; DROP TABLE postgres=# postgres=#
</source>
LEFT JOIN sample
<source lang="sql">
postgres=# postgres=# CREATE TABLE t1 ( postgres(# num int, postgres(# name varchar(10) postgres(# ); CREATE TABLE postgres=# postgres=# insert into t1 values(1,"a"); INSERT 0 1 postgres=# insert into t1 values(2,"b"); INSERT 0 1 postgres=# insert into t1 values(3,"c"); INSERT 0 1 postgres=# postgres=# CREATE TABLE t2 ( postgres(# num int, postgres(# name varchar(10) postgres(# ); CREATE TABLE postgres=# postgres=# insert into t2 values(1,"xxx"); INSERT 0 1 postgres=# insert into t2 values(3,"yyy"); INSERT 0 1 postgres=# insert into t2 values(5,"zzz"); INSERT 0 1 postgres=# postgres=# SELECT * FROM t1 LEFT JOIN t2 ON t1.num = t2.num;
num | name | num | name
+------+-----+------
1 | a | 1 | xxx 2 | b | | 3 | c | 3 | yyy
(3 rows) postgres=# postgres=# drop table t1; DROP TABLE postgres=# drop table t2; DROP TABLE postgres=#
</source>
LEFT JOIN two tables USING a column name
<source lang="sql">
postgres=# postgres=# CREATE TABLE t1 ( postgres(# num int, postgres(# name varchar(10) postgres(# ); CREATE TABLE postgres=# postgres=# insert into t1 values(1,"a"); INSERT 0 1 postgres=# insert into t1 values(2,"b"); INSERT 0 1 postgres=# insert into t1 values(3,"c"); INSERT 0 1 postgres=# postgres=# CREATE TABLE t2 ( postgres(# num int, postgres(# name varchar(10) postgres(# ); CREATE TABLE postgres=# postgres=# insert into t2 values(1,"xxx"); INSERT 0 1 postgres=# insert into t2 values(3,"yyy"); INSERT 0 1 postgres=# insert into t2 values(5,"zzz"); INSERT 0 1 postgres=# postgres=# SELECT * FROM t1 LEFT JOIN t2 USING (num);
num | name | name
+------+------
1 | a | xxx 2 | b | 3 | c | yyy
(3 rows) postgres=# postgres=# drop table t1; DROP TABLE postgres=# drop table t2; DROP TABLE postgres=#
</source>