SQL join to get the cartesian product of 2 columns out of 3 columns

Question

I have the following table:

create table #table (
  time int,
  key  char(1),
  val  int
)

with the following data:

insert into #table (time, key, val) values (0,"a",1)
insert into #table (time, key, val) values (0,"b",2)
insert into #table (time, key, val) values (1,"a",10)
insert into #table (time, key, val) values (2,"b",20)

and I would like to come up with a join of that will yield the following rows/cols:

0  a  1
0  b  2
1  a  10
1  b  0
2  a  0
2  b  20

Which is basically the cartesian product of the values of the first 2 columns, and their associated values in the 3rd column when the value exists, otherwise 0 when null.

I tried a few combinations of outer join but none of them seems to work.

Please help.

Thanks.

Answer 1

Try this:

SELECT DISTINCT t1.time, 
                t2.key, 
                IF(
                    t1.time = t2.time AND t1.key = t2.key AND t1.value = t2.value, 
                    t1.value, 
                    0
                ) AS value
FROM table_name t1
JOIN table_name t2
ORDER BY t1.time ASC,
         t2.key ASC;

Answer 2

Select times.time, keys.key, coalesce(table.val, 0) as val
From (Select distinct time from table) times
    cross join
    (Select distinct key from table) keys
    left outer join table
    on times.time = table.time and keys.key = table.key

Basically, your first construct the cross-product, then perform a left outer join.

Answer 3

Wow, this is the first time I've seen someone specifically ask for a Cartesian product result in a SQL Q&A forum!

I mean, a Cartesian product result is often mentioned in SQL Q&A forums when people answering want to talk about SQL's CROSS PRODUCT, or simply product in relational algebra (the operator name coined by Ted Codd).

I'm not from a mathematical background and I hadn't heard of Cartesian product until I started wanting to write better SQL and the term kept popping up in answers (anyone remember Usenet newsgroups?). I'd heard that SQL was based on mathematical set theory, again from people answering to say, "You should look for a set-based approach..." So at first I didn't think much about it beyond, "Cool, I learned a new term today."

Later -- perhaps a little too late -- I started to investigate the relational model (RM) on which SQL is based and found myself questioning my own understanding of Cartesian product.

Here's a line from Wikipedia on the subject:

for sets A and B, the Cartesian product A x B is the set of all ordered pairs (a, b) where a [is an element of] A and b [is an element of] B.

Erm, "ordered pairs"? I knew enough about RM to know that ordering is not appropriate. Briefly: in RM, an operation between two relations results in a relation, a relation has a heading, which is a set of attributes, and a set by definition has no ordering; while a relation can comprise an ordered pair attribute in the first place, it is not possible for an ordered pair to be materialized as the result of a relational operation.

SQL, on the other hand, has much left-to-right ordering dependecies (e.g. column ordering in UNION that was later fixed with UNION CORRESPONDING), so perhaps Cartesian product has some meaning in SQL? SQL is not a strict as RM but let's say an operation between two table expressions results in a table expression. When the operation is CROSS JOIN, is it true to say the result the set of all ordered pairs?

First, is the result of CROSS JOIN a set? Well, if the table expressions involved have duplicate rows then the result would also have duplicate rows, so it wouldn't strictly speaking be a set. However, if we have taken steps to ensure our table expressions conform to first normal form (and indeed we should) therefore the result of CROSS JOIN can be a set. [We have a similar problem and resolution with attribute names common to both table expressions.]

Second, is the result of CROSS JOIN a pair? Consider an example:

WITH Suppliers AS
     ( SELECT * FROM (
        VALUES
            ( 'S1', 'Smith', 'London' ),
            ( 'S2', 'Jones', 'Paris' )
        ) AS t ( SID, SNAME, CITY )
    ),
    Parts AS
     ( SELECT * FROM (
        VALUES
            ( 'S1', 'Nut', 'Red' ),
            ( 'S2', 'Bolt', 'Green' )
        ) AS t ( PID, SNAME, COLOR )
    )
SELECT *
  FROM Suppliers
       CROSS JOIN Parts;

The result is four rows of six columns (no duplicate column names). The columns are not grouped in any way. For me, there is nothing in the result to suggest I have a set of pairs.

Third, are the columns result of CROSS JOIN ordered? We can switch the tables...

SELECT *
  FROM Parts
       CROSS JOIN Suppliers;

...and, more than likely, the columns will appear in left-to-right ordering of Parts then Suppliers. Personally, I don't consider this to be 'ordered'. The SQL standard says words to the effect of "implementation defined" for SELECT *, to mean there is no guarantee of any inherent ordering. I don't think there are any knowledgeable people on SO who would recommend relying on any left-to-right column ordering in a result that isn't explicitly defined.

My conclusion is SQL lacks a true Cartesian product operator and that CROSS JOIN is just another SQL operation that results in a table expression (or similar). In the SQL context, we should stop using the term Cartesian product and instead use CROSS JOIN or simply product.

Answer 4

Thanks to simhumileco, here's the answer after making it Sybase-compatible.

Here's the creation and populating of the table:

create table #table (
  time int,
  fet  char(1),
  val  int
)

insert into #table (time, fet, val) values (0,"a",1)
insert into #table (time, fet, val) values (0,"b",2)
insert into #table (time, fet, val) values (1,"a",10)
insert into #table (time, fet, val) values (2,"b",20)

And the actual solution:

Select times.time, fets.fet, coalesce(#table.val, 0) as val
From (Select distinct time from #table) times
JOIN (Select distinct fet from #table) fets on 1=1
    left outer join #table
    on times.time = #table.time and fets.fet = #table.fet
order by times.time ASC,
         fets.fet ASC

Which yielded:

 time        fet val
 ----------- --- -----------
           0 a             1
           0 b             2
           1 a            10
           1 b             0
           2 a             0
           2 b            20

just like I wanted it.

Answer 5

in sql server query cross join gives the Cartesian product of two columns (if both tables are one column table).