42

Is it possible to select only some columns from a table on a JOIN? (Any kind.)

philipxy
  • 14,867
  • 6
  • 39
  • 83
Psyche
  • 8,513
  • 20
  • 70
  • 85

3 Answers3

56

Of course. Just list the columns you want to select as you would in any query:

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

Note that I've included the table1. or table2. prefix on all columns to be sure there aren't any ambiguities where fields with the same name exist in both tables.

VoteyDisciple
  • 37,319
  • 5
  • 97
  • 97
  • 4
    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 Gilmour Aug 25 '09 at 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 (...) – sixthgear Aug 25 '09 at 17:19
  • 1
    I want to select the columns from the table I'm about to join, not from the first table. – Psyche Aug 25 '09 at 17:20
  • @Psyche: It's no different. As Vorey depicts, table2.column3 is coming from the joined table, not the original source table. – Adam Robinson Aug 25 '09 at 17:21
53

Add a * to just that table in your select statement, separate from other columns with a comma:

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

Source: https://stackoverflow.com/a/3492919/3417198

jlansey
  • 3,996
  • 2
  • 17
  • 15
  • 2
    finally the answer I was looking for. Of course one can specify each column in each nested select but it often happens that you need to cascade 5 or more subqueries in order to append different columns to your main temporary table, and it is such a pain to rewrite and track all the columns in each subquery. This solves it – Radu Simionescu May 28 '20 at 14:19
12

If you want some of table1's columns and some of table2's columns, you would do something like

SELECT t1.col1, t1.col2, t1.col3, t2.col1, t2.col2, t2.col3
FROM table1 t1
LEFT JOIN table2 t2
ON...
bv8z
  • 965
  • 2
  • 9
  • 19