Select only some columns from a table on a LEFT JOIN

Asked
Viewd48001

19

Is it possible to select only some columns from a table on a LEFT JOIN?

3 个答案

8

在select语句的表中添加一个*,并用逗号将其与其他列分开:

 SELECT table1.*, table2.col2, table2.col3
FROM table1
LEFT JOIN table2
ON...
 

来源: https://stackoverflow.com/a/3492919/3417198

11

如果您想要table1的某些列和table2的某些列,则可以执行类似的操作

 SELECT t1.col1, t1.col2, t1.col3, t2.col1, t2.col2, t2.col3
FROM table1 t1
LEFT JOIN table2 t2
ON...
 
38

当然。只需列出要在任何查询中选择的列即可:

 SELECT table1.column1, table1.column2, table2.column3
FROM table1
LEFT JOIN table2 ON (...)
 

请注意,我在所有列上都添加了table1.table2.前缀,以确保两个表中都存在相同名称的字段。

  • I want to select the columns from the table I’m about to join, not from the first table.

    PsycheAugust 25, 2009 17:20
  • @Psyche: It’s no different. As Vorey depicts, table2.column3 is coming from the joined table, not the original source table.

    Adam RobinsonAugust 25, 2009 17:21
  • Probably worth adding that it’s a good idea to prefix them with the table they’re from e.g. table1.column1, table2.column2 etc so stop ambiguity errors and just for general readability.

    Gavin GilmourAugust 25, 2009 17:18
  • Also, if you have any ambiguous column names, you can specify which table to use with dot syntax: SELECT table1.id, table2.name FROM table1 LEFT JOIN table2 ON (…)

    sixthgearAugust 25, 2009 17:19