Is subtraction of inner join from full outer join some type of “join”?

up vote
0
down vote

favorite

Given two tables, is there a name for the result of subtracting inner join from full outer join, both on the same condition? Is it a type of "join"? Thanks.

asked Nov 10 at 22:25

Ben

1527

it's an anti-join which doesn't exist as syntax in SQL (don't know for Relational Theory), must be written using EXPECT or two NOT EXISTS
– dnoeth
Nov 10 at 22:42

What information are you trying to extract from the tables?
– Shawn
Nov 10 at 22:46

add a comment |

up vote
0
down vote

favorite

Given two tables, is there a name for the result of subtracting inner join from full outer join, both on the same condition? Is it a type of "join"? Thanks.

asked Nov 10 at 22:25

Ben

1527

it's an anti-join which doesn't exist as syntax in SQL (don't know for Relational Theory), must be written using EXPECT or two NOT EXISTS
– dnoeth
Nov 10 at 22:42

What information are you trying to extract from the tables?
– Shawn
Nov 10 at 22:46

add a comment |

up vote
0
down vote

favorite

Given two tables, is there a name for the result of subtracting inner join from full outer join, both on the same condition? Is it a type of "join"? Thanks.

asked Nov 10 at 22:25

Ben

1527

Given two tables, is there a name for the result of subtracting inner join from full outer join, both on the same condition? Is it a type of "join"? Thanks.

sql

asked Nov 10 at 22:25

Ben

1527

asked Nov 10 at 22:25

Ben

1527

asked Nov 10 at 22:25

Ben

1527

asked Nov 10 at 22:25

Ben

1527

asked Nov 10 at 22:25

Ben

1527

it's an anti-join which doesn't exist as syntax in SQL (don't know for Relational Theory), must be written using EXPECT or two NOT EXISTS
– dnoeth
Nov 10 at 22:42

What information are you trying to extract from the tables?
– Shawn
Nov 10 at 22:46

add a comment |

it's an anti-join which doesn't exist as syntax in SQL (don't know for Relational Theory), must be written using EXPECT or two NOT EXISTS
– dnoeth
Nov 10 at 22:42

What information are you trying to extract from the tables?
– Shawn
Nov 10 at 22:46

it's an anti-join which doesn't exist as syntax in SQL (don't know for Relational Theory), must be written using EXPECT or two NOT EXISTS
– dnoeth
Nov 10 at 22:42

What information are you trying to extract from the tables?
– Shawn
Nov 10 at 22:46

add a comment |

1 Answer
1

active

oldest

votes

up vote
1
down vote

It is not a type of join in SQL. You can write it as:

select . . .
from a full join
 b
 on a.id = b.id
where a.id is null or b.id is null;

If you are looking for ids that are in only one table, it can be more efficient to do:

select a.id
from a
where not exists (select 1 from b where b.id = a.id)
union all
select b.id
from b
where not exists (select 1 from a where a.id = b.id);

answered Nov 10 at 22:59

Gordon Linoff

745k32285390

add a comment |

Your Answer

StackExchange.ifUsing("editor", function ()
StackExchange.using("externalEditor", function ()
StackExchange.using("snippets", function ()
StackExchange.snippets.init();
);
);
, "code-snippets");

StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "1"
;
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function()
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled)
StackExchange.using("snippets", function()
createEditor();
);

else
createEditor();

);

function createEditor()
StackExchange.prepareEditor(
heartbeatType: 'answer',
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader:
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
,
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
);

);

draft saved

draft discarded

StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53244025%2fis-subtraction-of-inner-join-from-full-outer-join-some-type-of-join%23new-answer', 'question_page');

);

Post as a guest

Name

Required, but never shown

1 Answer
1

active

oldest

votes

1 Answer
1

active

oldest

votes

up vote
1
down vote

It is not a type of join in SQL. You can write it as:

select . . .
from a full join
 b
 on a.id = b.id
where a.id is null or b.id is null;

If you are looking for ids that are in only one table, it can be more efficient to do:

select a.id
from a
where not exists (select 1 from b where b.id = a.id)
union all
select b.id
from b
where not exists (select 1 from a where a.id = b.id);

answered Nov 10 at 22:59

Gordon Linoff

745k32285390

add a comment |

up vote
1
down vote

It is not a type of join in SQL. You can write it as:

select . . .
from a full join
 b
 on a.id = b.id
where a.id is null or b.id is null;

If you are looking for ids that are in only one table, it can be more efficient to do:

select a.id
from a
where not exists (select 1 from b where b.id = a.id)
union all
select b.id
from b
where not exists (select 1 from a where a.id = b.id);

answered Nov 10 at 22:59

Gordon Linoff

745k32285390

add a comment |

up vote
1
down vote

It is not a type of join in SQL. You can write it as:

select . . .
from a full join
 b
 on a.id = b.id
where a.id is null or b.id is null;

If you are looking for ids that are in only one table, it can be more efficient to do:

select a.id
from a
where not exists (select 1 from b where b.id = a.id)
union all
select b.id
from b
where not exists (select 1 from a where a.id = b.id);

answered Nov 10 at 22:59

Gordon Linoff

745k32285390

It is not a type of join in SQL. You can write it as:

select . . .
from a full join
 b
 on a.id = b.id
where a.id is null or b.id is null;

If you are looking for ids that are in only one table, it can be more efficient to do:

select a.id
from a
where not exists (select 1 from b where b.id = a.id)
union all
select b.id
from b
where not exists (select 1 from a where a.id = b.id);

answered Nov 10 at 22:59

Gordon Linoff

745k32285390

answered Nov 10 at 22:59

Gordon Linoff

745k32285390

answered Nov 10 at 22:59

Gordon Linoff

745k32285390

answered Nov 10 at 22:59

Gordon Linoff

745k32285390

add a comment |

draft saved

draft discarded

draft saved

draft discarded

Post as a guest

Name

Required, but never shown

Name

Required, but never shown

Name

Required, but never shown

This page is only for reference, If you need detailed information, please check here

搜尋此網誌

Myujth