Can I do a left join without returning the conditional columns?

Question

New to SQL but I want to be able to optimize my query by bringing just the right amount of data. I am doing a left join on CS Rep Name and WE, which are two columns present in both tables. I find that if I don't bring in CS Rep Name and WE in the TECDR table, the query would error. Is there a workaround to this? Since it is a left join, I don't need redundant data.

SELECT *
FROM   Tish_Email_CSAT_Dump AS TECD
       LEFT JOIN (SELECT CS_Rep_Name,
                         Team_Leader,
                         Operations_Manager,
                         Tenure,
                         WE,
                         FileName
                  FROM   Tish_Email_CSAT_Dump_Roster) AS TECDR
         ON TECD.CS_Rep_Name = TECDR.CS_Rep_Name
            AND TECD.WE = TECDR.WE

what do you mean with `don't bring in CS Rep Name and WE in the TECDR table`?, what do you actually want as a result? — Lamak, Aug 10 '16 at 17:55
avoid the subquery, join the two tables directly. Then limit the amount of data replacing select * for the columns you really need — vercelli, Aug 10 '16 at 17:58
@vercelli - the derived table doesn't make any actual difference except to clarity possibly. It will be resolved against the base tables anyway. — Martin Smith, Aug 10 '16 at 17:59
Possible duplicate of [MySQL Select all columns from one table and some from another table](http://stackoverflow.com/questions/3492904/mysql-select-all-columns-from-one-table-and-some-from-another-table) — Tab Alleman, Aug 10 '16 at 18:04

Sergey Kalinichenko · Answer 1 · 2016-08-10T18:17:42.123

3

When you embed a SELECT inside a query in place of a table, the result of a select (projection) behave like a table visible only inside the query.

In your case, the join is the same as if there were a table called TECDR with the columns that you select. Hence, if you leave out some columns of Tish_Email_CSAT_Dump_Roster from your SELECT, these columns would not be available for joining or selection.

However, in your case this is unnecessary: all you need to do is joining to the underlying table, like this:

SELECT
    TECD.*
,   TECDR.Team_Leader
,   TECDR.Operations_Manager
,   TECDR.Tenure
,   TECDR.FileName
FROM Tish_Email_CSAT_Dump AS TECD
LEFT JOIN Tish_Email_CSAT_Dump_Roster AS TECDR
       ON TECD.CS_Rep_Name = TECDR.CS_Rep_Name AND TECD.WE = TECDR.WE

edited Aug 10 '16 at 18:17

answered Aug 10 '16 at 17:58

Sergey Kalinichenko

714,442
84
1,110
1,523

@GordonLinoff Thank you very much for pointing this out! This is now fixed. – Sergey Kalinichenko Aug 10 '16 at 18:18
This makes sense to me and helps me understand what I did wrong. Thank you. Instead of select * I narrowed my query further. – Zeta Aug 10 '16 at 20:18

score 2 · Answer 2 · answered Aug 10 '16 at 17:57

2

select 
  <place the columns you want here>
from 
  Tish_Email_CSAT_Dump as TECD
  Left join Tish_Email_CSAT_Dump_Roster as TECDR 
    On TECD.CS_Rep_Name = TECDR.CS_Rep_Name and TECD.WE = TECDR.WE

answered Aug 10 '16 at 17:57

mikey

5,090
3
24
27

score 1 · Answer 3 · answered Aug 10 '16 at 18:02

Hope the following helps or else please share the query that errors:

select TECD.Column1, TECD.Column2, TECDR.Column1, TECDR.Column2 from Tish_Email_CSAT_Dump as TECD Left join Tish_Email_CSAT_Dump_Roster as TECDR On TECD.CS_Rep_Name = TECDR.CS_Rep_Name and TECD.WE = TECDR.WE

Can I do a left join without returning the conditional columns?

3 Answers3