[SOLVED] Cartesian products of tables that contains same columns

Cartesian products of tables that contains same columns

I am really confused about this. I searched a lot of tutorials but i couldnot find a clear answer.

A  B        B  D
1  X        x  5
2  x        y  6
            x  4

I want to cross this two tables.A , B, B,d are attributes.

A  B   B  D
1  X   x  5
2  x   x  5
1  X   y  6
2  x   y  6
1  X   x  4
2  x   x  4

This should be answer normally according to rule of cartesian. Cross all rows. But i am confused about same column B. Same column will seem twice?

Solution

Some relational query language/algebra relations have ordered column names. So an attribute name can name more than one column. (There should be a way to reference a column by column number.) The Cartesian product operator can take any two relations, because it is not a problem for an attribute name to appear more than once on output (or input).

Some relational query language/algebra Cartesian product operators change attribute names on output. Input relations have associated names. (The input either includes names of relation variables/constants or relation values contain names.) Each output column name is a combination of an input column name and an input relation name. The Cartesian product operator can take any two relations. Because columns that appear in different relations with the same attribute name on input will be distinguished by input relation name on output.

Some relational query languages/algebras have both. The Cartesian product operator can take any two relations.

Some relational query language/algebra relations have only one copy of an attribute name. So Cartesian product can only be called when inputs don't share any attribute names. (It is just a special case of NATURAL JOIN and/or EQUIJOIN.) If you have inputs sharing column names and you want output with a column for every input column then you need to rename columns so all are different before you join.

So you must pay close attention to what kind of relations and operators a relational query language/algebra has.

PS: SQL query language

The FROM clause of a SELECT expression makes a temporary Cartesian product of its tables. Every column name is an input column name prefixed by a given table name or table alias, plus a dot. This is the changes-names verions of Cartesian product above. The dotted names are used in the rest of the SELECT expression. The SELECT clause finally removes the prefixes and dots. So there are no dots in an SQL table value outside a SELECT expression. (The columns are also ordered, and there can be duplicate column names.)

If your SQL table expressions are AB & BD then you just need

SELECT * FROM AB ab CROSS JOIN BD bd