I'm a newby who's having trouble with what seems a very simple query. Basically I have these two tables with data as shown:
Table BOAT: ---------------- ID USER_ID 10 4 11 5 Table BOAT_USERS ----------------------- USER_ID NAME 1 Christina 2 James 3 Daisy 4 William 5 MikeAnd all I want to do is a query which will give me all the USER_IDs and NAMEs of the people who are NOT in the BOAT table. In other words, a query which could return ONLY the USER_IDs 1, 2, and 3, and NOT 4 and 5 which are already in the BOAT table. I tried the following query and several variations but it didn't work:
FROM BOAT_USERS U, BOAT B WHERE B.USER_ID != U.USER_IDSo can you help me with that?
You bet I can help! Here's the query you'll need:SELECT * FROM boat_users WHERE user_id NOT IN (SELECT user_id FROM boat) ;There are actually several different ways that you could write this query, but I figured this one would be relatively easy to understand.
First, note the sub-query (in parentheses in the WHERE clause). This query will run first and substitute the values it returns into the where clause of the outer query. The effect is something like this:SELECT * FROM boat_users WHERE user_id NOT IN (4, 5) ; <-- after the sub-query runs and substitutes it's resultSo that you get the result you want which is:USER_ID NAME ---------- ----------------- 1 Christina 2 James 3 DaisyHope that helps!
For More Information
- Dozens more answers to tough Oracle questions from Karen Morton are available.
- The Best Oracle Web Links: tips, tutorials, scripts, and more.
- Have an Oracle or SQL tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize. Submit your tip today!
- Ask your technical Oracle and SQL questions -- or help out your peers by answering them -- in our live discussion forums.
- Ask the Experts yourself: Our SQL, database design, Oracle, SQL Server, DB2, metadata, object-oriented and data warehousing gurus are waiting to answer your toughest questions.
Dig Deeper on Oracle and SQL
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.