CODEWITHSUNDEEP
sqloracle-database
Harshad
Harshad

Reputation: 733

Natural Join Scenario

When we join two tables using NATURAL JOIN, the join operation is performed on the basis on Common Column Names. When we have two colums with the same name in two tables, only the common records are displayed.

I have one query that when we have columns with the same name but different datatypes, those columns are also joined. I mean suppose column_1 in table1 has datatype INTEGER and column_1 in table2 has datatype VARCHAR, the join is performed on the basis of these two columns.

In short, how does the Natural Join takes place when there is a difference in datatypes of two columns with the same name ?

Upvotes: 3

Views: 1063

Answers (4)

Salman Khan
Salman Khan

Reputation: 1

Always keep in mind while using a natural join in a script or query .

  • The associated tables have one or more pairs of identically named columns.
  • The columns can have a different data type if implicit conversion is possible for all the rows of that column otherwise column will be excluded .
  • Don’t use ON clause in a natural join.

when specifying NATURAL JOIN ,the join is based on all columns with same name and " same date type OR different datatype : if implicit conversion is possible". when specifying JOIN USING only the specified columns get joined.

Natural joins is not a good practice to be used because

1.it will ignore the column if the implicit conversions fails in case of different data type but same name.

2.if the column names get renamed in future.

3.if others columns is added to the tables with same names

Upvotes: 0

Luke Liu
Luke Liu

Reputation: 298

If you use difference datatypes then you force data type conversion before match can be performed. That is extra work. On the other hand if no math is ever performed on the column and the value is not used as a number then it should be stored as character.

Once you pick a data type for an attribute then you should use that same data type for that attribute in all tables in which the column is defined. Otherwise you may end up with an implicit conversion.

Upvotes: 1

MatBailie
MatBailie

Reputation: 86775

NATURAL JOIN is commonly accepted as an anti-pattern.

It causes many maintainability issues. This is just one of them.

Instead go with explicit joins where you can type cast the fields.

  table1
INNER JOIN
  table2
    ON table2.fieldX = CAST(table1.fieldX AS INT)

As a rule of thumb, and personal preference, being explicit is much more maintainable than being implicit. And there are no performance issues.

Upvotes: 8

Tribhuwan
Tribhuwan

Reputation: 180

Datatypes don't have to match as long as they are convertible to each other. However if the datatypes are not consistent, the optimizer may not be able to use the indexes.

Upvotes: 1

Related Questions