PostgreSQL/Table Joins/Left Join

Материал из SQL эксперт
Перейти к: навигация, поиск

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>
Источник — «http://sqle.ru/index.php?title=PostgreSQL/Table_Joins/Left_Join&oldid=4447»