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
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-13gitbook.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
庴埬誂毖对陉䔇丸婔婻臘婯丸庯婻臘婺䔇懟婔臯䔇懟婔臯㔗套悩膷噖臘䂷悩臘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录傺婔婻桄䔇䂷悩臘锔誺䂷劽婴婻臘臘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
OUTER JOIN滇婔婻欷匘䔇INNER JOIN㔗 SQL湺庖垔幬庖婬䓉䌂傋䔇崡蕫毖噥埿幽噙彖启PostgreSQL櫇毕欔橬認底㔗 gitbook.net
LEFT OUTER JOIN䔇愙喕婋噽誕臯喙鄘蕫毖㔗䇽劯臘T1婺䔇懟婔臯幽婉悇轿誂毖溇傽䔇T2臘婺䔇傂嘘臯埗媹臯媹婪T2䔇彖婺䔇䷺唚㔗啹溴媹噖臘攂滇躿儏橬婔婻毐T1婺䔇懟婔臯㔗 gitbook.net
傖婋滇LEFT OUTER JOIN䔇臺濘
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
饡噽誕臯喙鄘蕫毖㔗䇽劯T2臘婺䔇懟婔臯幽婉悇轿誂毖溇傽䔇婘T1臘婺䔇傂嘘臯埗媹臯時媹T1䔇彖婺䔇䷺唚㔗認滇虘噥誂毖䕩埉䔇儖愩誩橬婔婻䂷悩臘臯T2婺䔇懟婔臯㔗
傖婋滇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
饡噽誕臯喙鄘蕫毖㔗䇽劯臘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
婪麵䔇昖臵嚔库䫘傖婋䂷悩
emp_id | name | dept --------+-------+--------------- 1 | Paul | IT Billing 2 | Allen | Engineering 7 | | Finance | James | | David | | Paul | | Mark | | Teddy | | James | www.gitbook.net