Member Avatar for dashawk

guys, can anyone help me on this problem? i have two tables then what I want is that, when an identical item is found in table2, it will not be displayed.

Example:

Table1                             Table2
item1                               other1
item2                               other2
item3                               item2
item4                               item3

Condition:
if we use this query: select * from Table1 the items from Table1 will appear but if the items of Table1 exists in Table2 then i must not be displayed.

how can i do that? thanks

  • 3 Contributors
  • 2 Replies
  • 283 Views
  • 9 Hours Discussion Span
  • Latest Post Latest Post by Reverend Jim

Dani AI

Generated

Goal: return rows from Table1 that do not appear in Table2 (based on the same column). For 's sample that means keeping only the values unique to Table1. The two most common, reliable patterns are an anti-join (LEFT JOIN ... IS NULL) or a correlated NOT EXISTS subquery; both avoid the pitfalls in 's earlier attempt and are clearer than relying on DISTINCT alone (as mentioned).

Left-join anti-join (simple, easy to read):

SELECT t1.item
FROM Table1 AS t1
LEFT JOIN Table2 AS t2
  ON t1.item = t2.item
WHERE t2.item IS NULL;

NOT EXISTS (robust against NULLs in the other table):

SELECT t1.item
FROM Table1 AS t1
WHERE NOT EXISTS (
  SELECT 1 FROM Table2 AS t2 WHERE t2.item = t1.item
);

Notes and quick troubleshooting:

  • Avoid NOT IN unless the subquery is guaranteed not to contain NULLs; NULLs make NOT IN behave unexpectedly. If NOT IN is used, filter NULLs from the subquery explicitly.
  • Specify column names (no SELECT * inside subqueries) and ensure joined columns share the same type and collation; mismatches can silently prevent matches.
  • For large tables add an index on the comparison column in Table2 and check the plan with EXPLAIN to confirm the optimizer is using the index.
  • If Table1 contains duplicates and only a single occurrence is needed, apply DISTINCT or GROUP BY after filtering.

These approaches fix the logic problem in the thread while handling common edge cases (NULLs, types, performance).

Recommended Answers

All 2 Replies

Member Avatar for thines01

select distinct
Or
use a union and make it distict.

Can you post your full query?

Edited by thines01 because: clarity
Member Avatar for Reverend Jim

You can try

select name from Table2 where name not in (select * from Table1)
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.