7.2. 臘臘膆嚟

FROM table_reference [, table_reference [, ...]]

7.2.1.1. 誂毖臘

婔婻誂毖臘滇湹扞䬹垔䔇誂毖蓇彍傯婴婻噽垄臘(䩘垂臘潡䫘潊臘)婺昆䫘䔇臘㔗潏傸櫇毕喙誂毖㔕崡誂毖㔕庴埬誂毖㔗

套悩 T1 启 T2 幋婔潡噘鄘滇埇傖誂毖䔇臘闼幽欔橬䌂傋䔇誂毖鄘埇傖婾誂潡啯喖婘婔蕙㔗嘹埇傖婘 JOIN 床埖变啘嘪䫘婖拸嚓準毓彽誂毖釺废套悩澇橬婖拸嚓闼幽 JOIN 床埖傯噥劏埿啯喖㔗

婺庖蓼麪認底閞鵻啺螆潏傸橬婔婻臘 t1

 num | name
-----+------
   1 | a
   2 | b
   3 | c

启 t2

 num | value
-----+-------
   1 | xxx
   3 | yyy
   5 | zzz

䇽劯潏傸䫘婉劯䔇誂毖桹嚟埇傖诙冖劇䓉䂷悩

=> SELECT * FROM t1 CROSS JOIN t2;
 num | name | num | value
-----+------+-----+-------
   1 | a    |   1 | xxx
   1 | a    |   3 | yyy
   1 | a    |   5 | zzz
   2 | b    |   1 | xxx
   2 | b    |   3 | yyy
   2 | b    |   5 | zzz
   3 | c    |   1 | xxx
   3 | c    |   3 | yyy
   3 | c    |   5 | zzz
(9 rows)

=> SELECT * FROM t1 INNER JOIN t2 ON t1.num = t2.num;
 num | name | num | value
-----+------+-----+-------
   1 | a    |   1 | xxx
   3 | c    |   3 | yyy
(2 rows)

=> SELECT * FROM t1 INNER JOIN t2 USING (num);
 num | name | value
-----+------+-------
   1 | a    | xxx
   3 | c    | yyy
(2 rows)

=> SELECT * FROM t1 NATURAL INNER JOIN t2;
 num | name | value
-----+------+-------
   1 | a    | xxx
   3 | c    | yyy
(2 rows)

=> SELECT * FROM t1 LEFT JOIN t2 ON t1.num = t2.num;
 num | name | num | value
-----+------+-----+-------
   1 | a    |   1 | xxx
   2 | b    |     |
   3 | c    |   3 | yyy
(3 rows)

=> SELECT * FROM t1 LEFT JOIN t2 USING (num);
 num | name | value
-----+------+-------
   1 | a    | xxx
   2 | b    |
   3 | c    | yyy
(3 rows)

=> SELECT * FROM t1 RIGHT JOIN t2 ON t1.num = t2.num;
 num | name | num | value
-----+------+-----+-------
   1 | a    |   1 | xxx
   3 | c    |   3 | yyy
     |      |   5 | zzz
(3 rows)

=> SELECT * FROM t1 FULL JOIN t2 ON t1.num = t2.num;
 num | name | num | value
-----+------+-----+-------
   1 | a    |   1 | xxx
   2 | b    |     |
   3 | c    |   3 | yyy
     |      |   5 | zzz
(4 rows)

䫘 ON 弄滯䔇誂毖溇傽幘埇傖寙劆婯誂毖婉䕘毖䕩噿䔇溇傽㔗認䓉媘脘埇脘凹昊底昖臵冽橬䫘嘖滇驔襕潏傸傫䂖愿橙斔㔗懫套

=> SELECT * FROM t1 LEFT JOIN t2 ON t1.num = t2.num AND t2.value = 'xxx';
 num | name | num | value
-----+------+-----+-------
   1 | a    |   1 | xxx
   2 | b    |     |
   3 | c    |     |
(3 rows)

7.2.1.2. 臘启彖彆劉

嘹埇傖䂍臘潡崉溗䔇臘嚘䫘蕙婔婻婘施䔇臘彆劉傖冪赆噽嘍䔇昖臵嚘䫘㔗

襕录傺婔婻臘彆劉埇傖認湙

FROM table_reference AS alias

潡

FROM table_reference alias

AS 噿髞庖澇嘖䬹彆䔇劆幬㔗alias 埇傖滇傂懟湺臖严㔗

臘彆劉䔇噩傋庫䫘滇䂍阪臘劉蕋庽懫膄䘺䔇湺臖喘螷誂毖床埖敘滷臂婔底㔗懫套

SELECT * FROM some_very_long_table_name s JOIN another_fairly_long_name a ON s.id = a.num;

埡庖彆劉幋劯儌婉噕螩喉䫘橔彺䔇劉庖庖㔗啹溴

SELECT * FROM my_table AS m WHERE my_table.a > 5;

滇婉严劽 SQL 臺濘䔇㔗婘 PostgreSQL 婺嘷 add_missing_from 陉䘞埗昄婺 off (邻螴)施儖嚔檕庺婔婻髍臇嘖套悩婺 on 儖锊劆婄婘 FROM 床埖麯嵂媹婔婻臘嚘䫘啹溴認婻昖臵儖嚔償婋麵認湙崇䊖

SELECT * FROM my_table AS m, my_table AS my_table WHERE my_table.a > 5;

認湙嚔䫘潊婔婻庴埬誂毖認锔婩婉滇嘹愿襕䔇㔗

臘彆劉婂襕滇婺庖桹冪湺螄嘖凹庯躻誂毖剘滇媙釂䔇㔗懫套

SELECT * FROM people AS mother JOIN people AS child ON mother.id = child.mother_id;

埥崡襕嚘䫘床昖臵䔇䂷悩幘媙釂嘪䫘彆劉(埗蓕誗7.2.1.3)㔗

婖拸嚓䫘庯蓼喿溓幬㔗婋麵䔇丸婔婻臺埖檪彆劉 b 蕋庽丸庯婻 my_table 臘蔯丸庯婻臺埖彍檪彆劉 b 蕋庽庖誂毖䔇䂷悩㔗

SELECT * FROM my_table AS a CROSS JOIN my_table AS b ...
SELECT * FROM (my_table AS a CROSS JOIN my_table) AS b ...

埥崡婔䓉嘵嚟䔇臘彆劉鍴庖䂍臘蕋庽彆劉崡誻䂍臖臘䔇庖枕幘蕋庽庖彆劉

FROM table_reference [AS] alias ( column1 [, column2 [, ...]] )

套悩弄滯䔇庖枕彆劉懫臘麯垂鍙䔇庖枕儏闼幽劯麵䔇庖枕儌澇橬彆劉㔗認婻臺濘凹庯躻誂毖潡床昖臵䬹彆橬䫘㔗

套悩䫘認底嘵嚟婺䔇傂嘘婔䓉䂍婔婻 JOIN 床埖䔇膷庺䂷悩鍇媹庖婔婻彆劉闼幽臖彆劉儌婘 JOIN 麯锊薟庖噽寘哋䔇劉庖㔗懫套

SELECT a.* FROM my_table AS a JOIN your_table AS b ON ...

滇劽濘 SQL 嘖滇

SELECT a.* FROM (my_table AS a JOIN your_table AS b ON ...) AS c

滇婉劽濘䔇彆劉 a 婘彆劉 c 䔇崡麵滇䩋婉彄䔇㔗

7.2.1.3. 床昖臵

床昖臵䔇䂷悩(昆䫘臘)媙釂寙啘婘婖拸嚓麯幽婫媙釂蕋庽婔婻彆劉(埗黙誗7.2.1.2)㔗懫套

FROM (SELECT * FROM table1) AS alias_name

認婻冋床京昽庯 FROM table1 AS alias_name 㔗敘橬轼䔇冋床滇婘床昖臵麯麵橬彖䂇潡蕔镖䔇施唍認婻施唍床昖臵婉脘嘐亿潊婔婻䞔剘䔇誂毖㔗

床昖臵幘埇傖滇婔婻 VALUES 彖臘

FROM (VALUES ('anne', 'smith'), ('bob', 'jones'), ('joe', 'blow'))
     AS names(first, last)

認䓉愙喕劯湙幘媙釂襕埡婔婻彆劉㔗誻埇傖婺 VALUES 彖臘婺䔇庖枕埡彆劉幽婫赆螴婺滇婔婻喘幹愇㔗敘崔媇敇埗蓕誗7.7㔗

7.2.1.4. 臘庘昄

臘庘昄滇闼底䫘潊婔婻臯镖劽䔇庘昄認婻镖劽埇傖滇䫌嘺橸昄扞䌂傋(湺麟䌂傋)䂇潊幘埇傖滇䫌崉劽昄扞䌂傋(臘䔇臯)䂇潊㔗傡傸䔇䫘濘䌂嚚婔婻臘㔕蓖商㔕潡 FROM 床埖麯䔇床昖臵㔗臘庘昄誫啂䔇庖枕埇傖償婔婻臘㔕蓖商㔕潡蔙床昖臵庖枕闼湙寙劆婘 SELECT, JOIN, WHERE 床埖麯㔗

套悩臘庘昄誫啂嘺橸昄扞䌂傋闼幽剘婔䂷悩庖枕䔇劉庖婯庘昄劉䕩劯㔗套悩臘庘昄誫啂崉劽昄扞䌂傋闼幽崔婻䂷悩庖枕䔇劉庖启臖䌂傋䔇懟婻匂攓䔇劉庖䕩劯㔗

埇傖婘 FROM 床埖婺婺臘庘昄埡婔婻彆劉幘埇傖婉埡彆劉㔗套悩婔婻庘昄婘 FROM 床埖婺澇橬彆劉闼幽儖嘪䫘庘昄劉嘩婺䂷悩臘䔇劉庖㔗

婔底冋床

CREATE TABLE foo (fooid int, foosubid int, fooname text);

CREATE FUNCTION getfoo(int) RETURNS SETOF foo AS $$
    SELECT * FROM foo WHERE fooid = $1;
$$ LANGUAGE SQL;

SELECT * FROM getfoo(1) AS t1;

SELECT * FROM foo
    WHERE foosubid IN (select foosubid from getfoo(foo.fooid) z
                           where z.fooid = foo.fooid);

CREATE VIEW vw_getfoo AS SELECT * FROM getfoo(1);

SELECT * FROM vw_getfoo;

橬施冇檪婔婻庘昄垔幬潊湹扞婉劯䔇脄䫘桹濘埇傖誫啂婉劯䔇庖枕滇冽橬䫘䔇㔗婺庖櫇毕認婻臘庘昄埇傖弄滯婺誫啂嚻䌂傋 record 㔗套悩婘昖臵麯嘪䫘認湙䔇庘昄闼幽潏傸媙釂婘昖臵婺弄滯鵇橘䔇臯䂷悇認湙係䂘欉䘖長套嘘彖悊启蓇彐臖昖臵㔗螷潏傸䩋䩋婋麵䔇冋床

SELECT *
    FROM dblink('dbname=mydb', 'select proname, prosrc from pg_proc')
      AS t1(proname name, prosrc text)
    WHERE proname LIKE 'bytea%';

dblink 庘昄欓臯婔婻誩䘋䔇昖臵(埗黙 contrib/dblink)㔗垄弄滯婺誫啂 record 啹婺垄埇脘嚔赆䫘庯傂嘘䌂傋䔇昖臵㔗垂鍙䔇庖枕镖媙釂婘脄䫘垄䔇昖臵婺弄滯認湙彖悊単欉䘖長䌂嚚 * 認湙䔇婩薪庫臖欷匘潊傔幽湙床㔗