Can a Join Happen with Same Attribute Names but Different Domains?

join;relational-algebra

Problem

When doing any sort of Join all common attributes are listed under one column in one way or another. However, I have not been able to find a source that deal with columns of the same name, but with different domains.

Put in a visual perspective, I am taking about columns like the case below:

enter image description here enter image description here

The domain is different because one B column is numbers but the other is letters.

Question

Is a Join possible with attributes of the same name but different domains?

Best Answer

I think you are being misled by your terminology.

SQL is a standard that has the following schematics

  • Schema
  • Table
  • Column
  • Attribute

*Oracle does not have separate databases in an Instance. schemas are tied to a user.

SQL is a 4th generation language that separates the need to know where to query an object to high, almost human language-style programming

When you write a Query in SQL, the compiler (called the SQL Optimizer) translates this into machine-level language and retrieves your data. It is therefore highly dependent on the Optimizer to correctly retrieve the data how it thinks is best.

What this means is that your SQL is nothing more than meaningful suggestions to the Optimizer. All you need to know is what the Structures’ names are, not where or how to grab it.

SQL has hints and Operators that help the Optimizer to pick the right datasets, but it is the Optimizer that chooses the ORDER of the Query to parse first.

Thus, you are writing Logical queries. If two tables are joined, it is your responsibility to help the Optimizer by specifically calling out the columns that can be joined.

Even if the attributes are not the same, the Optimizer is fully agile enough to know how to translate on the fly these values. For example, a VARCHAR type value of ‘13’ is still rather similar to a tinyint, integer, numeric Value, so the Optimizer will Implicitly convert these columns into the same data type!

The following example is in TSQL:

CREATE TABLE TableA (col_A int, col_B varchar(24) )
CREATE TABLE TableB (col_A varchar(24), col_B int)
INSERT INTO TableA (col_A, col_B)
VALUES (1, ‘My Value’)
INSERT INTO TableB (col_A, col_B)
VALUES (‘Not my Value’, 1)

SELECT A.Col_B
FROM TableA AS A
INNER JOIN TableB AS B ON A.Col_A = B.col_B

Notice we used an alias to help the optimizer know what we mean by the join and what we mean by which column to return.

Related Question