PostgreSQL䔇UNION床埖/誊䞖严䫘庯劽幽婴婻潡崔婻SELECT臺埖䔇䂷悩婉誫啂傂嘘麉崉䔇臯㔗 www.gitbook.net

䕘毖嘪䫘UNION懟婻SELECT锬拷䔇彖昄媙釂噙橬䕩劯䔇䕩劯昄䕞䔇彖臘膆嚟䕩劯䔇昄扞䌂傋幽螷垄傸婘䕩劯䔇釺废嘖垄傸婉媙滇䕩劯䔇阪庥㔗

臺濘:

UNION䔇嘺橸臺濘套婋 gitbook.net

SELECT column1 [, column2 ]
FROM table1 [, table2 ]
[WHERE condition]

UNION

SELECT column1 [, column2 ]
FROM table1 [, table2 ]
[WHERE condition] 
gitbook.net
 

認麯䂍垔䔇溇傽埇傖滇湹扞驔襕䔇傂嘘臘膆嚟㔗

冋床:

蔄荏傖婋婴婻臘COMPANY 臘套婋

gitbook.net

testdb=# SELECT * from COMPANY;
 id | name  | age | address   | salary
----+-------+-----+-----------+--------
  1 | Paul  |  32 | California|  20000
  2 | Allen |  25 | Texas     |  15000
  3 | Teddy |  23 | Norway    |  20000
  4 | Mark  |  25 | Rich-Mond |  65000
  5 | David |  27 | Texas     |  85000
  6 | Kim   |  22 | South-Hall|  45000
  7 | James |  24 | Houston   |  10000
(7 rows)
 
gitbook.net
 

埥婔臘滇DEPARTMENT 套婋 www.gitbook.net

testdb=# SELECT * from DEPARTMENT;
 id | dept        | emp_id
----+-------------+--------
  1 | IT Billing  |      1
  2 | Engineering |      2
  3 | Finance     |      7
  4 | Engineering |      3
  5 | Finance     |      4
  6 | Engineering |      5
  7 | Finance     |      6
(7 rows)
 
gitbook.net
 

䯄婘螷潏傸媹噖認婴婻臘嘪䫘SELECT臺埖誂劯UNION床埖套婋 www.gitbook.net

testdb=# SELECT EMP_ID, NAME, DEPT FROM COMPANY INNER JOIN DEPARTMENT
        ON COMPANY.ID = DEPARTMENT.EMP_ID
   UNION
     SELECT EMP_ID, NAME, DEPT FROM COMPANY LEFT OUTER JOIN DEPARTMENT
        ON COMPANY.ID = DEPARTMENT.EMP_ID; gitbook.net 

This would produce following result:

 emp_id | name  |  dept
--------+-------+--------------
      5 | David | Engineering
      6 | Kim   | Finance
      2 | Allen | Engineering
      3 | Teddy | Engineering
      4 | Mark  | Finance
      1 | Paul  | IT Billing
      7 | James | Finance
(7 rows)
 gitbook.net 

UNION ALL 床埖:

UNION ALL誊䞖严滇䫘準䂷劽婴婻SELECT臺埖寙拸麉崉臯䔇䂷悩㔗蕫䕘锗䫘劯湙䔇蓇彍锗䫘庯UNION ALL誊䞖严㔗 www.gitbook.net

臺濘:

UNION ALL䔇嘺橸臺濘套婋

www.gitbook.net

SELECT column1 [, column2 ]
FROM table1 [, table2 ]
[WHERE condition]

UNION ALL

SELECT column1 [, column2 ]
FROM table1 [, table2 ]
[WHERE condition] www.gitbook.net 

認麯䂍垔䔇溇傽埇傖滇傂嘘臘膆嚟湹扞驔襕㔗

www.gitbook.net

冋床:

䯄婘螷潏傸抺欋婪誄婴婻臘婺䔇SELECT臺埖套婋 www.gitbook.net

testdb=# SELECT EMP_ID, NAME, DEPT FROM COMPANY INNER JOIN DEPARTMENT
        ON COMPANY.ID = DEPARTMENT.EMP_ID
   UNION ALL
     SELECT EMP_ID, NAME, DEPT FROM COMPANY LEFT OUTER JOIN DEPARTMENT
        ON COMPANY.ID = DEPARTMENT.EMP_ID; www.gitbook.net 

認儖库䫘傖婋䂷悩 gitbook.net

 emp_id | name  | dept
--------+-------+--------------
      1 | Paul  | IT Billing
      2 | Allen | Engineering
      7 | James | Finance
      3 | Teddy | Engineering
      4 | Mark  | Finance
      5 | David | Engineering
      6 | Kim   | Finance
      1 | Paul  | IT Billing
      2 | Allen | Engineering
      7 | James | Finance
      3 | Teddy | Engineering
      4 | Mark  | Finance
      5 | David | Engineering
      6 | Kim   | Finance
(14 rows) www.gitbook.net