How do I return two columns from first table if only one column partially matches with the second one?

Question

select art.artno, art.name  from Art 
left outer join store on art.artno =store.artno  
where art.Artno not in (select art.artno from store)

The query is supposed to be like this but seems not working(I do not get any result rows beside the column names). Using a MSSQL 2008.

table art             table store                 EXPECTED RESULT 
artno   name          artno qty                  artno   name 
    1    xbox360          1   1                      2     XHW
    2    XHW              3   2                      5     PS2
    3    NETANDO          4   1
    5    PS2              6   3
    6    PS1 
    4    X1

How do I write a query to get the Expected out shown in the example?

Just to let you know the tables are 100plus K rows large if helps.

Finally some lights on why the above code does not work would be beneficial. I looked at this this link seems that outer join must work, may be I could not understand this at all.

I tried with full outer join as well, did not help. Using except I was able to find just the artno but did not let me produce the name column.

Tuncay Göncüoğlu · Accepted Answer · 2012-08-08T12:09:14.403

1

another approach could be

select
  a.`artno`,
  a.`name`
from
  art a
left join
  store s on s.artno=a.artno
where
  s.artno is null

On large tables, the second approach would most likely be better.

edited Aug 08 '12 at 12:09

answered Aug 08 '12 at 11:43

Tuncay Göncüoğlu

1,699
17
21

@tuncay , Thanks. The second option helped but not the first one, I cannot explain why, but if you do it would be helpful. – tough Aug 08 '12 at 12:06
Would you please let me know if the position of the `s.artno` and `s.artno` after on are related to `left` and `right` of of the `join` statement. – tough Aug 08 '12 at 14:01
I dont really think so. Its a condition and I specified explicit table aliases ("a" and "s"). – Tuncay Göncüoğlu Aug 08 '12 at 14:05
In the statement `left join store on store.artno=art.artno` if I change the position like `left join store on art.artno=store.artno` what option should I use with the `join`(`left join`or `right join`)? just to know, the problem is solved already just wanted to know ... – tough Aug 08 '12 at 14:20
I dont think the direction of the condition has any effect on the selected join type. so you could use `a.artno=s.artno` or `s.artno=a.artno` with same left join (or right join) – Tuncay Göncüoğlu Aug 08 '12 at 15:43

Suzanne Soy · Answer 2 · 2012-08-08T11:48:39.157

0

select art.artno, art.name from art
where art.artno not in (select store.artno from store)

You don't need to do any kind of join to use an inner query (the (select store.artno from store) in this case). The inner query is like building a temporary table, filling it with values, and using it in the query.

Since you want your inner query to give you all artno in the table store, you should use (select store.artno from store) and not (select art.artno from store), since I think that would select the art.artno from the outer query, without taking into account the contents of the store table.

edited Aug 08 '12 at 11:48

answered Aug 08 '12 at 11:42

Suzanne Soy

3,027
6
38
56

This seems not working I have tried it as well. But don´t know why? Seems must be joined to get the result like that. – tough Aug 08 '12 at 11:50
This works fine with sqlite3. I can't test with MSSQL since I don't have it, sorry. – Suzanne Soy Aug 08 '12 at 11:56

Gene · Answer 3 · 2012-08-08T21:59:13.753

0

Georges answer works, but on tables of that size a correlated subquery with a 'not exists' will be quicker.

Tested, my subqueries were slower than left outer. It's the way forward

edited Aug 08 '12 at 21:59

answered Aug 08 '12 at 11:44

Gene

11
3

@Tuncay and Gene : Yes, but it depends a lot on the work done by the optimizer. For example, if you have an index on store.artno and another on art.artno, then I think the sqlite engine will behave with an inner query as fast as if you had made a join. – Suzanne Soy Aug 08 '12 at 11:51
as tables get larger, subqueries will invariably get slower, when compared to joins. I usually try to avoid subqueries where I can. Lesson thaught by MySQL version<4. – Tuncay Göncüoğlu Aug 08 '12 at 12:01
@gene, Thanks, This would take large query time, so I tried it and took more than a min and stopped it. Sorry I had to edit the query I initially presented to match the given example. – tough Aug 08 '12 at 12:09

How do I return two columns from first table if only one column partially matches with the second one?

3 Answers3

Linked