䯄婘嘉䘞饡釕 > 昄扞康 > PostgreSQL > PostgreSQL JOINS床埖

PostgreSQL JOINS床埖

準溊寘录桺䆹    䫌 悕垵幥 敘桄䬽橸    敟蓽庺渇

PostgreSQL䔇蕫毖Joins床埖䫘庯傯婴婻潡崔婻昄扞康婺䔇臘䔇螄嘘䕩䂷劽㔗婔婻JOIN滇婔䓉欋枕傯婴婻臘婺嘪䫘婩蓕庯懟婻唚䕩䂷劽䔇庖枕㔗

gitbook.net

婘PostgreSQL䔇Join 䌂傋滇: www.gitbook.net

  • CROSS JOIN www.gitbook.net

  • INNER JOIN www.gitbook.net

  •  LEFT OUTER JOIN gitbook.net

  •  RIGHT OUTER JOIN

    www.gitbook.net

  •  FULL OUTER JOIN gitbook.net

婘潏傸嚔哋幋嬉螷潏傸蔄荏婴婻臘COMPANY 启DEPARTMENT㔗潏傸噾䂟䩋彄庖INSERT臺埖準准噙COMPANY臘㔗欔傖埻滇螷潏傸啺螆噸埩臘䔇螄嘘彖臘 www.gitbook.net

 id | name  | age | address   | salary | join_date
----+-------+-----+-----------+--------+-----------
  1 | Paul  |  32 | California|  20000 | 2001-07-13
  3 | Teddy |  23 | Norway    |  20000 |
  4 | Mark  |  25 | Rich-Mond |  65000 | 2007-12-13
  5 | David |  27 | Texas     |  85000 | 2007-12-13
  2 | Allen |  25 | Texas     |        | 2007-12-13
  8 | Paul  |  24 | Houston   |  20000 | 2005-07-13
  9 | James |  44 | Norway    |   5000 | 2005-07-13
 10 | James |  45 | Texas     |   5000 | 2005-07-13 

gitbook.net

埥婔嚹臘滇鄘閘橬傖婋垔幬 gitbook.net

CREATE TABLE DEPARTMENT(
   ID INT PRIMARY KEY      NOT NULL,
   DEPT           CHAR(50) NOT NULL,
   EMP_ID         INT      NOT NULL
); 
www.gitbook.net

認麯滇准噙DEPARTMENT臘䔇INSERT臺埖彖臘

www.gitbook.net

INSERT INTO DEPARTMENT (ID, DEPT, EMP_ID)
VALUES (1, 'IT Billing', 1 );

INSERT INTO DEPARTMENT (ID, DEPT, EMP_ID)
VALUES (2, 'Engineering', 2 );

INSERT INTO DEPARTMENT (ID, DEPT, EMP_ID)
VALUES (3, 'Finance', 7 ); 
www.gitbook.net

橔劯潏傸噾䂟毬䙓鄘閘臘婺䔇螄嘘彖臘 gitbook.net

 id | dept        | emp_id
----+-------------+--------
  1 | IT Billing  |  1
  2 | Engineering |  2
  3 | Finance     |  7 www.gitbook.net 

CROSS JOIN

庴埬誂毖对陉䔇丸婔婻臘婯丸庯婻臘婺䔇懟婔臯䔇懟婔臯㔗套悩膷噖臘䂷悩臘x启y彖彖彆橬X + Y彖㔗䫌庯庴埬蕫毖橬埇脘库䫘麂婩崓䔇臘媙釂脘景埻婘锗嘷䔇施唍嘪䫘垄傸㔗 gitbook.net

傖婋滇CROSS JOIN䔇臺濘

gitbook.net

SELECT ... FROM table1 CROSS JOIN table2 ... www.gitbook.net 

湹扞婪麵䔇臘婪潏傸埇傖喍婔婻庴埬誂毖套婋 www.gitbook.net

testdb=# SELECT EMP_ID, NAME, DEPT FROM COMPANY CROSS JOIN DEPARTMENT; gitbook.net 

婪麵䔇昖臵嚔库䫘傖婋䂷悩 www.gitbook.net

emp_id| name  |  dept
------|-------|--------------
    1 | Paul  | IT Billing
    1 | Teddy | IT Billing
    1 | Mark  | IT Billing
    1 | David | IT Billing
    1 | Allen | IT Billing
    1 | Paul  | IT Billing
    1 | James | IT Billing
    1 | James | IT Billing
    2 | Paul  | Engineering
    2 | Teddy | Engineering
    2 | Mark  | Engineering
    2 | David | Engineering
    2 | Allen | Engineering
    2 | Paul  | Engineering
    2 | James | Engineering
    2 | James | Engineering
    7 | Paul  | Finance
    7 | Teddy | Finance
    7 | Mark  | Finance
    7 | David | Finance
    7 | Allen | Finance
    7 | Paul  | Finance
    7 | James | Finance
    7 | James | Finance
 www.gitbook.net 

INNER JOIN

INNER JOIN录傺婔婻桄䔇䂷悩臘锔誺䂷劽婴婻臘臘1启臘2湹扞蕫毖脷臉䔇彖唚㔗昖臵懫膄table1婺䔇懟婔臯懟婔臯臘2欆彄欔橬凹悇轿誂毖脷臉䔇臯㔗嘷蕫毖脷臉悇轿table1启table2懟婻凹䕩对陉䔇臯彖䔇唚劽幽潊婔婻䂷悩臯㔗 gitbook.net

喙鄘蕫毖蕫毖䌂傋滇橔婩蓕䔇幽婫滇邻螴䔇蕫毖䌂傋㔗旘埇傖嘪䫘埇锬INNER噿髞庖㔗 www.gitbook.net

傖婋滇INNER JOIN䔇臺濘 www.gitbook.net

SELECT table1.column1, table2.column2...
FROM table1
INNER JOIN table2
ON table1.common_filed = table2.common_field; gitbook.net 

湹扞婪麵䔇臘潏傸埇傖喍婔婻喙鄘蕫毖套婋 www.gitbook.net

testdb=# SELECT EMP_ID, NAME, DEPT FROM COMPANY INNER JOIN DEPARTMENT
        ON COMPANY.ID = DEPARTMENT.EMP_ID; 

www.gitbook.net

婪麵䔇昖臵嚔库䫘傖婋䂷悩

gitbook.net

 emp_id | name  | dept
--------+-------+------------
      1 | Paul  | IT Billing
      2 | Allen | Engineering
 www.gitbook.net 

LEFT OUTER JOIN

OUTER JOIN滇婔婻欷匘䔇INNER JOIN㔗 SQL湺庖垔幬庖婬䓉䌂傋䔇崡蕫毖噥埿幽噙彖启PostgreSQL櫇毕欔橬認底㔗 gitbook.net

LEFT OUTER JOIN䔇愙喕婋噽誕臯喙鄘蕫毖㔗䇽劯臘T1婺䔇懟婔臯幽婉悇轿誂毖溇傽䔇T2臘婺䔇傂嘘臯埗媹臯媹婪T2䔇彖婺䔇䷺唚㔗啹溴媹噖臘攂滇躿儏橬婔婻毐T1婺䔇懟婔臯㔗 gitbook.net

傖婋滇LEFT OUTER JOIN䔇臺濘

gitbook.net

SELECT ... FROM table1 LEFT OUTER JOIN table2 ON conditional_expression ... 

gitbook.net

湹扞婪麵䔇臘婪潏傸埇傖喍婔婻喙鄘蕫毖套婋 gitbook.net

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

婪麵䔇昖臵嚔库䫘傖婋䂷悩

www.gitbook.net

 emp_id | name  | dept
--------+-------+------------
      1 | Paul  | IT Billing
      2 | Allen | Engineering
        | James |
        | David |
        | Paul  |
        | Mark  |
        | Teddy |
        | James |
 
www.gitbook.net

RIGHT OUTER JOIN

饡噽誕臯喙鄘蕫毖㔗䇽劯T2臘婺䔇懟婔臯幽婉悇轿誂毖溇傽䔇婘T1臘婺䔇傂嘘臯埗媹臯時媹T1䔇彖婺䔇䷺唚㔗認滇虘噥誂毖䕩埉䔇儖愩誩橬婔婻䂷悩臘臯T2婺䔇懟婔臯㔗

www.gitbook.net

傖婋滇LEFT OUTER JOIN䔇臺濘

www.gitbook.net

SELECT ... FROM table1 RIGHT OUTER JOIN table2 ON conditional_expression ... 

www.gitbook.net

湹扞婪麵䔇臘婪潏傸埇傖喍婔婻喙鄘蕫毖套婋

www.gitbook.net

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

婪麵䔇昖臵嚔库䫘傖婋䂷悩

gitbook.net

 emp_id | name  | dept
--------+-------+--------
      1 | Paul  | IT Billing
      2 | Allen | Engineering
      7 |       | Finance
 gitbook.net 

FULL OUTER JOIN

饡噽誕臯喙鄘蕫毖㔗䇽劯臘T1婺䔇懟婔臯幽婉悇轿誂毖溇傽䔇T2臘婺䔇傂嘘臯埗媹臯媹婪T2䔇彖婺䔇䷺唚㔗溴崡T2䔇懟婔臯幽婉悇轿誂毖溇傽䔇T1婺䔇傂嘘臯婘T1䔇彖䫘䷺唚時媹婔婻誂毖臯㔗

gitbook.net

傖婋滇FULL OUTER JOIN䔇臺濘 www.gitbook.net

SELECT ... FROM table1 FULL OUTER JOIN table2 ON conditional_expression ... gitbook.net 

湹扞婪麵䔇臘婪潏傸埇傖喍婔婻喙鄘蕫毖套婋 www.gitbook.net

testdb=# SELECT EMP_ID, NAME, DEPT FROM COMPANY FULL 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 |       | Finance
        | James |
        | David |
        | Paul  |
        | Mark  |
        | Teddy |
        | James | www.gitbook.net 
橸䆍桺䆹鍴濘滯蘸蘘崡庺婺橸䆍寘录潡䚡臏
渵誯傂嘘嘵嚟䔇蘸蘘嘖臙媇媙濘滯庺崇優麉傡庺媿媘嚹携庥幹嘺䇔昍䘋
蘸蘘臙濘滯桺䆹蘸蘘躻悕垵幥 [http://www.gitbook.net]
橸桺湺鵻PostgreSQL JOINS床埖
蘸蘘臙媺䘍寘桺鷆毖:http://www.gitbook.net/html/postgresql/2013/080569.html
婪婔䇺PostgreSQL亥溘溇傽      婋婔䇺PostgreSQL UNIONS床埖