BillTr
asked on
sql advice
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
#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
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.
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.
Yup, Steve is right. Forgot the "LEFT"
http://explainextended.com/2009/09/18/not-in-vs-not-exists-vs-left-join-is-null-mysql/
http://explainextended.com/2009/09/18/not-in-vs-not-exists-vs-left-join-is-null-mysql/
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
#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.
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.
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.
MySQL does not support EXCEPT or MINUS.
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
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
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
@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.
>>" 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.
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.
@PortletPaul: d'oh. sorry. There should be a NOT in that sentence: "[..] it does NOT imply [..]"
:) I did wonder, I much prefer this version "[..] it does NOT imply [..]"
ASKER
Really helpful...Thanks!
SELECT columns FROM A JOIN B ON A.column = B.column
WHERE B.someothercolumn IS NULL
HTH,
Dan