[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

sql advice

Posted on 2014-04-14
14
Medium Priority
?
253 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 23

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
Learn how to optimize MySQL for your business need

With the increasing importance of apps & networks in both business & personal interconnections, perfor. has become one of the key metrics of successful communication. This ebook is a hands-on business-case-driven guide to understanding MySQL query parameter tuning & database perf

 
LVL 23

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

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

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

Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
Backups and Disaster RecoveryIn this post, we’ll look at strategies for backups and disaster recovery.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
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…
Suggested Courses

656 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