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

Answer 1

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

Answer 2

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