Using subselect to accomplish LEFT JOIN

Posted by Andre on Stack Overflow See other posts from Stack Overflow or by Andre
Published on 2010-03-17T23:36:49Z Indexed on 2010/03/17 23:41 UTC
Read the original article Hit count: 297

Filed under:

subselect

|

subquery

|

sql

|

multiple-columns

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.

© Stack Overflow or respective owner

Related posts about subselect

NULL handling with subselect in Hibernate Criteria API

as seen on Stack Overflow - Search for 'Stack Overflow'
I'm constructing a Hibernate Criterion, using a subselect as follows DetachedCriteria subselect = DetachedCriteria.forClass(NhmCode.class, "sub"); // the subselect selecting the maximum 'validFrom' subselect.add(Restrictions.le("validFrom", new Date())); // it should be in the past (null needs… >>> More
Using subselect to accomplish LEFT JOIN

as seen on Stack Overflow - Search for 'Stack Overflow'
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… >>> More
mysql subselect alternative

as seen on Stack Overflow - Search for 'Stack Overflow'
Hi, Lets say I am analyzing how high school sports records affect school attendance. So I have a table in which each row corresponds to a high school basketball game. Each game has an away team id and a home team id (FK to another "team table") and a home score and an away score and a date. I… >>> More
How to Prove that using subselect queries in SQL is killing performance of server

as seen on Stack Overflow - Search for 'Stack Overflow'
One of my jobs it to maintain our database, usually we have troubles with lack of performance while getting reports and working whit that base. When I start looking at queries which our ERP sending to database I see a lot of totally needlessly subselect queries inside main queries. As I am not member… >>> More
Nhibernate many to many criteria query with subselect

as seen on Stack Overflow - Search for 'Stack Overflow'
I have a simple example of a blog: a Post table, a Tag table and a Post_Tag_MM lookup table linking the two tables. I use this hql query in order to fetch all posts, that DONT have some tags: var result = session .CreateQuery(@" … >>> More

Related posts about subquery

subquery factoring questions.

as seen on Stack Overflow - Search for 'Stack Overflow'
Hi, Please explain. a) is "subquery factoring" used to replace a non-correlated subquery? What about correlated subquery? b) if it is true, are "subquery" and "subquery factoring" executed exactly once? c) "subquery" vs "subquery factoring" which one is better Thank you. >>> More
Can we write subquery in between SELECT and FROM

as seen on Stack Overflow - Search for 'Stack Overflow'
i want to know, how to write subquery in between SELECT and FROM as SELECT Col_Name,(Subquery) From Table_Name Where Some_condition >>> More
Need Help with SQL Subquery

as seen on Stack Overflow - Search for 'Stack Overflow'
Hey: I am trying to write a query that will return all orders that only have a Subscription included. It is easy enough to write a query that includes all Orders with Subscriptions, another that includes all orders without a Subscription and then compare them with an unmatched query. But I don't… >>> More
Is possible to reuse subqueries?

as seen on Stack Overflow - Search for 'Stack Overflow'
Hello, I'm having some problems trying to perform a query. I have two tables, one with elements information, and another one with records related with the elements of the first table. The idea is to get in the same row the element information plus several records information. Structure could be explain… >>> More
Performance considerations for common SQL queries

as seen on Geeks with Blogs - Search for 'Geeks with Blogs'
Originally posted on: http://geekswithblogs.net/NibblesAndBits/archive/2013/10/16/performance-considerations-for-common-sql-queries.aspxSQL offers many different methods to produce the same results. There is a never-ending debate between SQL developers as to the “best way” or the “most efficient… >>> More