?
Solved

sql advice

Posted on 2014-04-14
14
Medium Priority
?
252 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Does Your Cloud Backup Use Blockchain Technology?

Blockchain technology has already revolutionized finance thanks to Bitcoin. Now it's disrupting other areas, including the realm of data protection. Learn how blockchain is now being used to authenticate backup files and keep them safe from hackers.

 
LVL 22

Accepted Solution

by:
Steve Wales earned 1400 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 35

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 49

Assisted Solution

by:PortletPaul
PortletPaul earned 600 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 35

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 49

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 35

Expert Comment

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

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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

By, Vadim Tkachenko. In this article we’ll look at ClickHouse on its one year anniversary.
In this article, we’ll look at how to deploy ProxySQL.
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…

765 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