Select only some columns from a table on a JOIN

Question

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

Toxicant

Answer 1

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.

Aubrey

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. – Stuffy

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 (...) – Damage

I want to select the columns from the table I'm about to join, not from the first table. – Toxicant

@Psyche: It's no different. As Vorey depicts, table2.column3 is coming from the joined table, not the original source table. – Bridgehead

Answer 2

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://mcmap.net/q/86606/-mysql-select-all-columns-from-one-table-and-some-from-another-table

Kaiak

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 – Peltate

Answer 3

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...

Beeves

Answer 4

This can be useful if you are facing data integrity issues e.g. product has same ID but different descriptions across 2 tables / data sources A work-around is to use CASE statement in your query Following query will Return Rows where col a is matching across both tables but column b may not be the same in table t2.

-- CREATE SAMPLE DATA
CREATE TABLE IF NOT EXISTS public.t1
(
    a text,
    b text,
    c text,
    d text
);
    
CREATE TABLE IF NOT EXISTS public.t2
(
    a text,
    b text,
    c text,
    d text
);
    
--truncate t1;
--truncate t2;
insert into t1 values ('1-1','1-1','1-c1','1-d1');
insert into t1 values ('1-2','1-2','1-c2','1-d2');
insert into t1 values ('1-3','1-3','1-c3','1-d3');
insert into t1 values ('1-1','1-4','1-c4','1-d4');
insert into t2 values ('1-1','1-1','2-c1','2-d1');
insert into t2 values ('1-2','1-2','2-c2','2-d2');
insert into t2 values ('1-3','2-b3','2-c3','2-d3');
insert into t2 values ('2-a4','2-b4','2-c4','2-d4');

-- QUERY THAT YOU ARE POSSIBLY LOOKING FOR
select * from
(
    select 
t1.a as a1, t1.b as b1, t1.c as c1, t1.d as d1
, t2.a as a2, 
case 
when t1.b <> t2.b then NULL
else t2.b
END b2,
case 
when t1.b <> t2.b then NULL
else t2.c
END c2,
case 
when t1.b <> t2.b then NULL
else t2.d
END d2

-- ,t2.c as c2, t2.d as d2
from t1
LEFT join t2 on t1.a = t2.a 
    ) as x
-- where b2 is null -- This is in case you want only rows where first key matches but second does not

Frightful