CODEWITHSUNDEEP
sql
kylex
kylex

Reputation: 14406

Please help explain this SQL statement

The table Arc(x,y) currently has the following tuples (note there are duplicates): 

(1,2),
(1,2),
(2,3),
(3,4),
(3,4),
(4,1),
(4,1),
(4,1),
(4,2)

Compute the result of the query:

SELECT a1.x, a2.y, COUNT(*)
FROM Arc a1, Arc a2
WHERE a1.y = a2.x
GROUP BY a1.x, a2.y;

What are a1 and a2 referring to?

Upvotes: 0

Views: 3230

Answers (3)

Quassnoi
Quassnoi

Reputation: 425863

It's called a self join.

You may join a table with itself, as in your task, it will result in:

(1, 2) (2, 3)  // note the join condition, y from the right is equal to x from the left
(1, 2) (2, 3)
(2, 3) (3, 4)
(2, 3) (3, 4)
...

etc.

This will of course shrink after GROUP'ing.

a1 and a2 refer to the instances of the table. From which part of the result you want the x? Use a1.x for the left part, a2.x for the right part.

Upvotes: 4

Jonathan Leffler
Jonathan Leffler

Reputation: 755104

The answer to the query is:

create temp table arc(x int, y int);
insert into arc values(1,2);
insert into arc values(1,2);
insert into arc values(2,3);
insert into arc values(3,4);
insert into arc values(3,4);
insert into arc values(4,1);
insert into arc values(4,1);
insert into arc values(4,1);
insert into arc values(4,2);

SELECT a1.x, a2.y, COUNT(*)
FROM Arc a1, Arc a2
WHERE a1.y = a2.x
GROUP BY a1.x, a2.y;

      4           3                1
      4           2                6
      2           4                2
      1           3                2
      3           1                6
      3           2                2

This is not necessarily the answer to the question, though. As already noted, 'a1' and 'a2' are table aliases, alternative names for the Arc table so that it can be joined to itself.

Upvotes: 0

GregD
GregD

Reputation: 7000

a1 and a2 are just aliases for the Arc table which is being joined to itself. You could also say,

Arc As a1, Arc As a2

Is that what you're asking?

Upvotes: 7

Related Questions