Using subselect to accomplish LEFT JOIN

sql subquery multiple-columns
Andre picture Andre · Mar 18, 2010 · Viewed 57.1k times · Source

Is is possible to accomplish the equivalent of a LEFT JOIN with subselect where multiple columns are required. Here's what I mean.

SELECT m.*, (SELECT * FROM model WHERE id = m.id LIMIT 1) AS models FROM make m

As it stands now doing this gives me a 'Operand should contain 1 column(s)' error.

Yes I know this is possible with LEFT JOIN, but I was told it was possible with subselect to I'm curious as to how it's done.

Answer

mechanical_meat picture mechanical_meat · Mar 18, 2010

There are many practical uses for what you suggest.

This hypothetical query would return the most recent release_date (contrived example) for any make with at least one release_date, and null for any make with no release_date:

SELECT m.make_name, 
       sub.max_release_date
  FROM make m
       LEFT JOIN 
           (SELECT id, 
                   max(release_date) as max_release_date
              FROM make 
           GROUP BY 1) sub
       ON sub.id = m.id

Related questions

Nested select statement in SQL Server

Why doesn't the following work? SELECT name FROM (SELECT name FROM agentinformation) I guess my understanding of SQL is wrong, because I would have thought this would return the same thing as SELECT name FROM agentinformation Doesn't the inner select …

Read More
Join vs. sub-query

I am an old-school MySQL user and have always preferred JOIN over sub-query. But nowadays everyone uses sub-query, and I hate it; I don't know why. I lack the theoretical knowledge to judge for myself if there is any difference. …

Read More
updating table rows in postgres using subquery

Using postgres 8.4, My goal is to update existing table: CREATE TABLE public.dummy ( address_id SERIAL, addr1 character(40), addr2 character(40), city character(25), state character(2), zip character(5), customer boolean, supplier boolean, partner boolean ) WITH ( OIDS=FALSE ); Initially i tested my query …

Read More