Solved

sql advice

Posted on 2014-04-14
14
250 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 35

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 35

Expert Comment

by:Dan Craciun
ID: 39999681
0
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 
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 35

Expert Comment

by:Dan Craciun
ID: 40000144
MySQL does not support EXCEPT or MINUS.
0
 
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

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

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

This guide whil teach how to setup live replication (database mirroring) on 2 servers for backup or other purposes. In our example situation we have this network schema (see atachment). We need to replicate EVERY executed SQL query on server 1 to…
I have been using r1soft Continuous Data Protection (http://www.r1soft.com/linux-cdp/) for many years now with the mySQL Addon and wanted to share a trick I have used several times. For those of us that don't have the luxury of using all transact…

726 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