CODEWITHSUNDEEP
sql
Deb Martin
Deb Martin

Reputation: 61

Joining columns with different types

Can I join a column of a table that is an Integer type with a column of another table that is a BigInteger type? If so, would I need to cast or add anything special?

Upvotes: 1

Views: 45

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269973

In general, SQL engines will do a conversion between bigint and int silently. I'm pretty sure that most will convert the int to a bigint. It is possible that your particular database might choose to convert both to int -- leaving open the possibility of an overflow error.

Often, conversion of types prevents the use of indexes. This can have a serious performance impact, if the index is needed.

Otherwise, there is no logical issue. Except. Most joins are on foreign keys. Foreign keys should be properly declared -- and they need to have the same type. So, you might want to investigate your data model, because in a proper data model, this would almost never be a question that needs to be answered.

Upvotes: 1

Obsidian
Obsidian

Reputation: 3897

Can I join a column of a table that is an Integer type with a column of another table that is a BigInteger type? If so, would I need to cast or add anything special?

Yes you can, since whether you use JOIN or a cartesian product (SELECT … FROM table1,table2), what you actually need is a condition that can be evaluated as true. For instance :

 SELECT table1.*,
        table2.*

   FROM table1
   JOIN table2
     ON table1.id = table2.id

In the very last row of this query, this condition will be verified to decide the inclusion of the candidate tuple. Most of the type, you'll have the benefit of an implicit cast, but you'll need to care by yourself about special cases.

Upvotes: 0

Related Questions