Solved

sql advice

Posted on 2014-04-14
14
246 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 32

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
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 
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 32

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 32

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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

All XML, All the Time; More Fun MySQL Tidbits – Dynamically Generate XML via Stored Procedure in MySQL Extensible Markup Language (XML) and database systems, a marriage we are seeing more and more of.  So the topics of parsing and manipulating XM…
Both Easy and Powerful How easy is PHP? http://lmgtfy.com?q=how+easy+is+php (http://lmgtfy.com?q=how+easy+is+php)  Very easy.  It has been described as "a programming language even my grandmother can use." How powerful is PHP?  http://en.wikiped…
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…

757 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

22 Experts available now in Live!

Get 1:1 Help Now