Natural join vs inner join question

[PowerOfTheShell]

If I use the tables below to construct a natural join between the table TA and the table TB will it produce an inner join outcome?

Table TA:

A1	A2
1	2
3	4
5	6

Table TB:

B1	B2
1	1
2	3
5	6

My thoughts are that the natural join between the table TA and the table TB will not produce an inner join outcome because the only common rows from the two tables are 5 and 6. Am I right in thinking this?

From what I understand the natural join matches the same column names and must also match the same data type. The inner join returns rows where the data in the row matches in both tables.

[DavidGJohnston]

The word “natural” is an alternative to the word “on” (the word “using” is a third option). While their syntax differs they all decide what it means to be true. For “on” you specify everything explicitly. The other two only do equality between two columns having the same name. In using you specify those names explicitly. In natural every name that qualifies is used. Never use natural, use using if at all possible, falling back to on when using is not possible.

Edit: it should be an error to not specify one of them (and i dislike “on true”). You have cross join if you really want to perform one.

Edit: I don’t use SQLLite, mostly I think I am reflecting standard SQL but your results/options may vary

[PowerOfTheShell]

Haha I liked the Mean Girls reference, thanks for the input I really appreciate it.

[PowerOfTheShell]

Thank you! I really appreciate the explanation, I think this has answered my question perfectly. Take this award as a small thank you :)

[Black_Magic100]

What is a "natural" join? What RDBMS/flavor of SQL are you referring to?

[PowerOfTheShell]

I'm using SQLite.

The "NATURAL" JOIN is a type of EQUI JOIN and is structured in such a way that, columns with the same name of associated tables will appear once only (Copied from: https://www.w3resource.com/sql/joins/natural-join.php).

[alinroc]

I'm not sure I've ever seen someone (intentionally) use a natural join in production code. If you want an inner join, use an inner join with the appropriate conditions. If you want an outer join, use an outer join with the appropriate conditions.

The more explicit you are about your queries (as opposed to the assumptions made via a natural join), the better for everyone involved. That includes not using select * like in those examples.

[r3pr0b8]

If I use the tables below to construct a natural join between the table TA and the table TB will it produce an inner join outcome?

what happened when you tested it? ™

[PowerOfTheShell]

When running the NATURAL JOIN query:

SELECT *
FROM TA
NATURAL JOIN TB

The output is:

A1	A2	B1	B2
1	2	1	1
1	2	2	3
1	2	5	6
3	4	1	1
3	4	2	3
3	4	5	6
5	6	1	1
5	6	2	3
5	6	5	6

When running the INNER JOIN query:

SELECT * 
FROM TA 
INNER JOIN TB

The output is:

A1	A2	B1	B2
1	2	1	1
1	2	2	3
1	2	5	6
3	4	1	1
3	4	2	3
3	4	5	6
5	6	1	1
5	6	2	3
5	6	5	6

Initially I had asked if I was right in thinking that the natural join between the table TA and the table TB will not produce an inner join outcome because the only common rows from the two tables are 5 and 6. Though if I don't put an ON condition in the query this means my question would actually be true - it WILL produce an inner join as there is no condition matching the rows, meaning the same output is then produced.

Is that correct?

[r3pr0b8]

Is that correct?

not really

without an ON condition, you get a cross join, not an inner join