Stack Overflow
Stack Overflow

Questions tagged [natural-join]

A natural join is a relational operation that joins on the like-named attributes between tables

Natural join (⋈) is a binary operator that is written as (R⋈S) where R and S are relations. The result of the operation is the set of all combinations of tuples in R and S that have equal values on their common attribute names. The resulting relation has a heading consisting of the set-union of the attributes from R and S, i.e. there are no duplicate attribute names in the result.

If the joined relations have no attribute names in common then R⋈S is equivalent to R×S (Cartesian product).

The natural join of any relation, R, to itself is R (R⋈R = R).

The ISO Standard SQL syntax for natural join (R⋈S) is:

SELECT * FROM R NATURAL JOIN S;
116 questions
216
votes
11 answers

Difference between natural join and inner join

What is the difference between a natural join and an inner join?
smith
  • 4,811
  • 8
  • 29
  • 38
23
votes
2 answers

Join tables on columns of composite foreign / primary key in a query

CREATE TABLE subscription ( magazine_id bigint, user_id bigint, PRIMARY KEY (magazine_id, user_id) ); CREATE TABLE delivery ( magazine_id bigint, user_id bigint, FOREIGN KEY (subscription) REFERENCES subscription…
samol
  • 13,640
  • 23
  • 69
  • 113
10
votes
6 answers

Is NATURAL (JOIN) considered harmful in production environment?

I am reading about NATURAL shorthand form for SQL joins and I see some traps: it just takes automatically all same named column-pairs (use USING to specify explicit column list) if some new column is added, then join output can be "unexpectedly"…
Grzegorz Szpetkowski
  • 35,042
  • 4
  • 82
  • 127
10
votes
3 answers

Oracle USING clause best practice

Disclaimer: I'm a developer and not a DBA. I've been a huge fan of the USING clause in Oracle since I accidentally stumbled upon it and have used it in place of the old-fashioned ON clause to join fact tables with dimension tables ever since. To me,…
RAY
  • 6,104
  • 5
  • 35
  • 65
7
votes
4 answers

Natural join with more than one common attribute in two tables

I can understand how natural join works when the two tables have only one common attribute. What if they have two ones? Table 1 have 3 attributes: A, B, C Table 2 has 3 attribute: A, B, D First two rows in table 1: 1 2 3 4 5 6 First two rows in…
Hiep
  • 79
  • 1
  • 2
  • 4
7
votes
4 answers

maximum and minimum number of tuples in natural join

I came across a question that states Consider the following relation schema pertaining to a students database: Student (rollno, name, address) Enroll (rollno, courseno, coursename) where the primary keys are shown underlined. The number of…
user1765876
5
votes
1 answer

mysql natural join not working

I have two tables in mysql server. I use these tables for studing JOIN multiple tables but something appears to be incorrect: mysql> select * from category; +-------------+-----------+ | category_id | name | +-------------+-----------+ | …
Влад Христенко
  • 111
  • 1
  • 4
5
votes
2 answers

Difference between cross product (cross join, Cartesian product) and natural join

While writing in SQL, how would I know if I should use cross product (cross join, Cartesian product) or natural join?
AamKhayega
  • 81
  • 1
  • 1
  • 3
5
votes
1 answer

SQLite natural join broken?

I am just getting to know NATURAL JOIN and SQLite is not behaning as I expect. SELECT * FROM r1 NATURAL JOIN (r2 NATURAL JOIN r3); and SELECT * FROM (r1 NATURAL JOIN r2) NATURAL JOIN r3; produce the same (correct) results. However if I omit the…
Panayiotis Karabassis
  • 2,130
  • 3
  • 21
  • 40
4
votes
2 answers

Does natural join distribute over set-difference?

In other words, is it true that: r1 ⋈ (r2 - r3) = r1 ⋈ r2 - r1 ⋈ r3 where r1 r2 and r3 are relations If it isn't what is the example?
Eyal
  • 1,640
  • 1
  • 16
  • 31
4
votes
4 answers

Oracle Natural Joins and Count(1)

Does anyone know why in Oracle 11g when you do a Count(1) with more than one natural join it does a cartesian join and throws the count way off? Such as SELECT Count(1) FROM record NATURAL join address NATURAL join person WHERE status=1 AND code =…
Aaron Smith
  • 3,202
  • 4
  • 26
  • 24
3
votes
4 answers

Lossless Join Property

Can someone please explain to me what is meant by the lossless join property in a relation schema? Is it the ability to maintain the semantics of information/data during the decomposition of relations whilst normalising?
user559142
  • 11,063
  • 45
  • 109
  • 177
3
votes
2 answers

What is the difference between Join and Natural Join?

I'm learning Oracle SQL and now I'm stuck on Joins chapter. I can't understand the difference between Join and Natural Join SELECT employee_id, job_id, department_id, e.last_name, e.hire_date, j.end_date FROM …
user6611026
3
votes
2 answers

can't get natural join to work

I'm using postgresql, though I confirmed this in sqlfiddle.com too. My tables and elements are: create table Publisher(pID int PRIMARY KEY, name varchar(255), address varchar(255)); create table Book(ISBN int PRIMARY KEY, name varchar(255), genre…
TugRulz
  • 65
  • 7
3
votes
0 answers

What is USING keyword in SQL Server 2008?

I know the beautiful usage of the SQL clause "USING": it is similar to NATURAL JOIN but you must detail the join columns; you can join more simply (and faster) the tables that have the same key and foreign key, and the output of queries doesn't…
Alberto
  • 2,572
  • 5
  • 29
  • 63
1
2 3 4 5 6 7 8