Query for Selecting except values with two column

Question

i have two tables. First table column value is 1,2,7. Second table column value is 1,2,3,4,5,6,7,8,9,10.

what i needed is i want to fetch second table values except first table values.Result should be 3,4,5,6,8,9,10.I do no what is the query for this one.Please help me.

possible duplicate of [Mysql: Select rows from a table that are not in another](http://stackoverflow.com/questions/11767565/mysql-select-rows-from-a-table-that-are-not-in-another) — Emil, Aug 04 '15 at 14:46
You have plenty of options... `EXCEPT`, `NOT IN`, `JOIN ON <>` etc... — Siyual, Aug 04 '15 at 14:46

score 1 · Answer 1 · answered Aug 04 '15 at 14:46

1

The standard SQL is to use NOT IN or NOT EXISTS:

select t2.*
from t2
where not exists (select 1 from table1 t1 where t1.value = t2.value);

answered Aug 04 '15 at 14:46

Gordon Linoff

1,242,037
58
646
786

score 1 · Accepted Answer · answered Aug 04 '15 at 14:46

1

SELECT value FROM secondtable WHERE value NOT IN (SELECT value FROM firsttable)

answered Aug 04 '15 at 14:46

drmarvelous

1,663
10
18

SELECT accname FROM m_costacc WHERE accname NOT IN (SELECT m_AccHeadID FROM m_accounthead_fa) .I used this query but im getting empty result – lalithkumar Aug 04 '15 at 15:02
NOT IN (SELECT m_AccHeadID as accname FROM m_accounthead_fa) – drmarvelous Aug 04 '15 at 15:03
Kindly mark the answer that worked for you so that others can benefit from the question as well. Thanks. – drmarvelous Aug 04 '15 at 16:07

Query for Selecting except values with two column

2 Answers2