The opposite of INNER JOIN

Question

I have query with Inner Join.

Query 1:

   select *
     from vehicle_models vmodel
     Inner join ogpo_voilure_model md on md.Name = vmodel.VEHICLE_MODEL

Now, i Need data that not exists in these id. With another word - opposite Inner JOIN.

I tried to make query that I need, but not successfull.

Query 2:

Select top 500 *
 from ogpo_voilure_model md
 Where md.id not in
 (
 select md.id
     from Novelty.dbo.vehicle_models vmodel
     Inner join [ogpo_voilure_model] md on md.Name = vmodel.VEHICLE_MODEL
 )

I find in StakOverflow answer like this(sixth example). But my fields are not NULL.

How I can achieve it?

Well the opposite to INNER is...surprise...OUTER. Try it out. — flowit, Oct 09 '15 at 10:14
@flowit: results of outer join would be a superset of those of inner join, not the complement. — Sergio Tulentsev, Oct 09 '15 at 10:18
@ШыназАлиш: what exactly are you trying to accomplish here? — Sergio Tulentsev, Oct 09 '15 at 10:19
@ШыназАлиш provide sample data and desired results than. — Stanislovas Kalašnikovas, Oct 09 '15 at 10:19
@SergioTulentsev, See the sixth image of [this link](http://stackoverflow.com/a/406333/4732301), but my ids are not null. — Шыназ Алиш, Oct 09 '15 at 10:24
is it possible to prepare a sqlfiddle (http://sqlfiddle.com/) for better understanding ? — Kishore Sahasranaman, Oct 09 '15 at 10:26
@ШыназАлиш: your "query 2" seems like it should work. Why didn't it? If you create a sample dataset on sqlfiddle, we'll take a stab at it. — Sergio Tulentsev, Oct 09 '15 at 10:26
@SergioTulentsev, My **Query 2** returns me 0 results. But I know that result must be more than **10k**. — Шыназ Алиш, Oct 09 '15 at 10:30
So have you tried the sixth example? You only get NULL fields when for one value there is no adequate join partner. This depends on your data. — flowit, Oct 09 '15 at 10:31
The reason you are getting 0 rows maybe in that your `Query 1` contains records with `md.id` is `NULL`. In this case clause `Where md.id not in (select ...)` always fails. — Kirill Kin, Oct 09 '15 at 10:42

jean · Answer 1 · 2015-10-09T10:51:09.870

The inner join means you want everything is in set table vehicle_models but also got one or more correlated row in ogpo_voilure_model

If you define the oposite to it as everything is in vehicle_models but don't got a correlated in ogpo_voilure_model

All you needs is:

select *
     from [Novelty].[dbo].[vehicle_models] vmodel
     Where vmodel.VEHICLE_MODEL not in
     (
       select md.Name
       from [Novelty].[dbo].[ogpo_voilure_model] md
     )

And following that definition it's right even if results returns zero rows. If it's not the right answer you must first define what the opposite of inner join means to you. For example you maybe want to swap the tables.

score 0 · Answer 2 · edited May 23 '17 at 12:14

What do you mean by "my fields are not NULL"?

The link that you gave in the question has an answer: FULL JOIN.

SELECT *
FROM 
    vehicle_models vmodel
    FULL JOIN ogpo_voilure_model md on md.Name = vmodel.VEHICLE_MODEL
WHERE
    md.Name IS NULL
    OR vmodel.VEHICLE_MODEL IS NULL
;

This will return rows from vehicle_models and ogpo_voilure_model that don't have common Vehicle Model Name.

It would help if you could add few sample rows to the question and your expected result.

The opposite of INNER JOIN

2 Answers2