Opposite Of An Inner Join Query

sql-server tsql inner-join outer-join
Nick LaMarca picture Nick LaMarca · Oct 28, 2010 · Viewed 55k times · Source

can someone help me write sql for a scernerio like this:

Table 1

2 columns: ID, Name

Table 2

2 columns: ID, Name

I want a query to show names from Table 1 that are not in table 2. So filter out all the names in table 1 that are in table 2 is the result query. Use ID for the filtering not name.

This will help me in what I am trying to do. Thanks in advance

Answer

Andrew picture Andrew · Oct 28, 2010 
Select * from table1
left join table2 on table1.id = table2.id
where table2.id is null

Related questions

Is having an 'OR' in an INNER JOIN condition a bad idea?

In trying to improve the speed of an immensely slow query (several minutes on two tables with only ~50,000 rows each, on SQL Server 2008 if it matters), I narrowed down the problem to an OR in my inner join, as in: …

Read More
Conditional Inner Join

I want to be able to inner join two tables based on the result of an expression. What I've been trying so far: INNER JOIN CASE WHEN RegT.Type = 1 THEN TimeRegistration ELSE DrivingRegistration AS RReg ON RReg.RegistreringsId = R.Id …

Read More
WHERE Clause vs ON when using JOIN

Assuming that I have the following T-SQL code: SELECT * FROM Foo f INNER JOIN Bar b ON b.BarId = f.BarId; WHERE b.IsApproved = 1; The following one also returns the same set of rows: SELECT * FROM Foo f INNER JOIN …

Read More