Join table on itself without two queries

Question

I've seen people recommending cross joining a table on itself by doing this:

SELECT *
FROM tbl AS A, tbl AS B
WHERE A.col1 = 1 AND B.col1 = 1

But here, the engine needs to iterate through all of the rows in tbl twice to match the two queries to the results of A and B, despite the fact that the queries (and therefore the results) are the same.

Assuming that the WHERE on A and B will always be the identical for the two, this is a waste. Is there any way to query for something once, and then cross join the result of that query on itself? I'd like to avoid temp tables, which would require disk writing instead of performing this entire thing in RAM.

I am using MySQL, although any SQL answer would help a lot.

EXAMPLE:

Suppose that tbl looks as follows:

COL1    COL2
1       A
1       B
1       C
2       D
2       E

When I run my where clause of col1 = 1, it returns the first three rows from the above table. What I want is the following table, but with only one execution of the where statement, since the two tables A and B are identical:

A.COL1    A.COL2    B.COL1    B.COL2
1         A         1         A
1         A         1         B
1         A         1         C
1         B         1         A
1         B         1         B
1         B         1         C
1         C         1         A
1         C         1         B
1         C         1         C

could you give examples of how you might structure the resultset from your query? — northpole, Mar 08 '12 at 01:48
You could use a table variable instead of a temp table: http://www.sql-server-performance.com/2007/temp-tables-vs-variables/ — sazh, Mar 08 '12 at 01:54

score 2 · Answer 1 · answered Mar 08 '12 at 02:46

You are basically asking for an intentional Cartesian join

select
      a.col1,
      a.col2,
      b.col1,
      b.col2
   from
      tbl a
         join tbl b
            on a.col1 = b.col1
   where
      a.col1 = 1
   order by
      a.col2,
      b.col2

To exactly hit your output order sequence, you need the order by by the "a" column 2 then "b" column 2

Jeremy Holovacs · Answer 2 · 2012-03-08T03:15:02.390

I really recommend avoiding that JOIN syntax... it can be very difficult to read.

Your explanation of what you are trying to do is a bit cryptic. The query as written offers no value for a JOIN operation. Generally speaking, when you want to JOIN a table to itself, it's on different columns:

select *
from tbl as a
inner join
table as b
on a.col1 = b.col2
where 
a.col1 = 1;

This allows you to query against the table, and also collect related information organized in a hierarchical fashion in the same table. For example:

create table tbl (
person_id int,
parent_id int
);

In this case, a parent is a person too. If you wanted to get a list of the parents related to the person with an ID of 1, you could write:

select
person.person_id as OriginalPerson,
parent.person_id as Parent
from
tbl as person
inner join
tbl as parent
on parent.person_id = person.person_id
where
person.person_id = 1;

UPDATE Upon reading your further explanation, you want a cartesian product:

select a.*, b.*
from tbl as a
inner join tbl as b
on 1=1
where a.col1 = 1
and b.col1 = 1

That just returns a join of everything in tbl against everything else. There are no conditions. — carlbenson, Mar 08 '12 at 02:47
so you only wanted it if col1 = 1? I thought you were just using that as a misguided JOIN condition. In that case, the double WHERE clause is not a waste; it is in fact absolutely necessary. — Jeremy Holovacs, Mar 08 '12 at 03:16

Join table on itself without two queries

2 Answers2