# 与内部连接相反的是[英] Opposite of inner join

• 完整

## 使用左JOIN/IS NULL

```   SELECT p.*
FROM PERSON p
```

## 使用不存在

```SELECT p.*
FROM PERSON p
WHERE NOT EXISTS(SELECT NULL
```

## 不使用

```SELECT p.*
FROM PERSON p
```

## 其他推荐答案

```Select ...
From Table1
Left Join Table2
On Table2.ForeignKeyCol = Table1.PrimaryKeyCol
Where Table2.PrimaryKeyCol Is Null
```

```Select ...
From Person
```

## 其他推荐答案

```SELECT p.PersonName, a.Address
FROM people p
```

```SELECT p.PersonName
FROM people p
```

```SELECT PersonName
FROM people
```

### 问题描述

What will be the opposite of inner join? For a table table Person (int PersonId, varchar PersoName, int AddrId), I want to know the rows in Person with bad AddrId which don't have a row in the Address table.

## 推荐答案

What will be the opposite of inner join?

An OUTER join, which can be of three options:

• LEFT
• RIGHT
• FULL

This is a good visual representation of JOINs

I want to know the rows in Person with bad AddrId which don't have a row in the Address table.

## Using LEFT JOIN/IS NULL

```   SELECT p.*
FROM PERSON p
```

## Using NOT EXISTS

```SELECT p.*
FROM PERSON p
WHERE NOT EXISTS(SELECT NULL
```

## Using NOT IN

```SELECT p.*
FROM PERSON p
```

## 其他推荐答案

An Inner join is not diametric to an Outer Join. They serve different purposes. However, a common pattern to find rows from one table that do not exist in another is to use an Outer Join:

```Select ...
From Table1
Left Join Table2
On Table2.ForeignKeyCol = Table1.PrimaryKeyCol
Where Table2.PrimaryKeyCol Is Null
```

This returns all rows from Table1 and any matching rows from Table2 such that if a given Table1 row has no Table2 match, a null for the Table2 columns are returned. By then requiring that a non-nullable column (Table2.PrimaryKeyCol) be Null, I will get all rows from Table1 that do not exist in Table2. Using your example table names we would have something like:

```Select ...
From Person
```

## 其他推荐答案

If you consider an inner join as the rows of two tables that meet a certain condition, then the opposite would be the rows in either table that don't.

For example the following would select all people with addresses in the address table:

```SELECT p.PersonName, a.Address
FROM people p
```

I imagine the "opposite" of this would be to select all of the people without addresses and all addresses without people. However this doesn't seem to be what you are asking, you seem to be interested in just one component of this: all the people without an address in the addresses table.

For this a left join would be best:

```SELECT p.PersonName
FROM people p
```SELECT PersonName