Solved

sql advice

Posted on 2014-04-14
14
247 Views
Last Modified: 2014-04-16
Was asked 2 sql related questions in a job interview the other day. I obviously didn't get the job. Here are the questions:

#1. I have 2 tables, A & B, and I want to return the rows from A that are NOT on B? How would I do it in one select statement. I didn't think a <> on a join would work and said I thought a subquery might be required. In Access I would have tagged set a column on A and flagged the rows that did = B. But I'm sure there must be a better solution than that.

#2. How would I control the sort on nested queries.

It's worth noting that this was for a BSA job…not a developer.

Thanks
0
Comment
Question by:BillTr
  • 3
  • 3
  • 3
  • +4
14 Comments
 
LVL 34

Expert Comment

by:Dan Craciun
ID: 39999649
#1. A faster replacement for NOT IN is a JOIN with a NULL test:
SELECT columns FROM A JOIN B ON A.column = B.column
WHERE B.someothercolumn IS NULL

HTH,
Dan
0
 
LVL 22

Expert Comment

by:Steve Wales
ID: 39999672
That won't work unless it's a left join - left join return nulls on the "B" table where rows in B don't exist.

A join like your example will always return only the matched rows - unless the table has nulls in the someothercolumn by design, it won't work.

Might be faster if the joined columns are indexed, don't know that it's necessarily true of there's not indexes involved.
0
 
LVL 34

Expert Comment

by:Dan Craciun
ID: 39999681
0
 
LVL 22

Accepted Solution

by:
Steve Wales earned 350 total points
ID: 39999697
Answering the question from the OP, a NOT IN, NOT EXISTS or the aforementioned LEFT JOIN / IS NULL will do the trick.

SELECT col1, col2
FROM   table1 a
WHERE  NOT EXISTS (
   SELECT 1            
   FROM   table 2 b
   WHERE  a.somecolumn = b.somecolumn
   );

or

SELECT col1, col2
FROM   table1 a
WHERE  somecolumn NOT IN (
   SELECT somecolumn            
   FROM   table 2 b
   );


Not quite sure what the second question is supposed to indicate
0
 
LVL 33

Expert Comment

by:ste5an
ID: 39999738
#2: Nested queries are likely to behave like a table. Tables are basically only an unordered set of rows. Thus sort order must be applied in the outer statement.
0
 
LVL 13

Expert Comment

by:magarity
ID: 40000141
Does MySQL not support the "minus" operator? If it does, the easiest way to answer #1 is:
select * from tableA minus select * from tableB

#2 is a trick question: what good would it possibly do to order the results of a nested query? ste5an is right; if you do want those values ordered after they get out of the nested query, you can order them in the outermost statement. But not within.
0
 
LVL 34

Expert Comment

by:Dan Craciun
ID: 40000144
MySQL does not support EXCEPT or MINUS.
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 15

Expert Comment

by:Ess Kay
ID: 40000238
i would do
SELECT * FROM TABLEA
WHERE id NOT IN (SELECT ID FROM TABLEB)



For sort, just name the column to sort by

SELECT * FROM TABLEA (A) INNEJOIN
   (SELECT * FROM TABLEB SORT BY SOMECOLUMN ASC)B ON A.ID = B.ID
0
 
LVL 48

Assisted Solution

by:PortletPaul
PortletPaul earned 150 total points
ID: 40001168
#1. my answer would have been, in preference
1. outer join (usually left outer join) with NULL
2. not exists ()
3. not in()

#2. How would I control the sort on nested queries.
This is a trick question. It will depend on what type of subquery, how it is being used in a larger query, and it can also depend on the dbms vendor.

In many cases it does not matter what the order is of subqueries and,  depending on dbms, you can be prohibited from using ORDER BY in subqueries.

Occasionally however ordering a subquery may be important, or even required, let's say we are using SQL Server and "SELECT TOP 1" - which implies that an order be applied to the data - in which case you would simply use an ORDER BY clause within that subquery.

& the likely reason for the question is:
For performance, one should only use ORDER BY when it is really needed.
0
 
LVL 33

Expert Comment

by:ste5an
ID: 40001301
@PortletPaul: While a ORDER BY in a nested query is necessary for a LIMIT() or TOP clause, it does imply that the result set is ordered when further processed.
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 40001325
>>" it does imply that the result set is ordered when further processed."
not always, and never to be relied upon.

e.g. if using a correlated subquery within the select clause such as

select
  t.id, t.name, (select top 1 name from sometab where id = t.id order by somefields) as x
from bigtab as t

the overall resultset has not been ordered at all

and the only reliable method to order the final resultset is to use the order by clause in the outermost query.
0
 
LVL 33

Expert Comment

by:ste5an
ID: 40001680
@PortletPaul: d'oh. sorry. There should be a NOT in that sentence: "[..] it does NOT imply [..]"
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 40002774
:)  I did wonder, I much prefer this version "[..] it does NOT imply [..]"
0
 

Author Closing Comment

by:BillTr
ID: 40004266
Really helpful...Thanks!
0

Featured Post

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Foreword In the years since this article was written, numerous hacking attacks have targeted password-protected web sites.  The storage of client passwords has become a subject of much discussion, some of it useful and some of it misguided.  Of cou…
Introduction In this installment of my SQL tidbits, I will be looking at parsing Extensible Markup Language (XML) directly passed as string parameters to MySQL 5.1.5 or higher. These would be instances where LOAD_FILE (http://dev.mysql.com/doc/refm…
Concerto provides fully managed cloud services and the expertise to provide an easy and reliable route to the cloud. Our best-in-class solutions help you address the toughest IT challenges, find new efficiencies and deliver the best application expe…
Need to grow your business through quality cloud solutions? With everything required to build a cloud platform and solution, you may feel like the distance between you and the cloud is quite long. Help is here. Spend some time learning about the Con…

911 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now