Self join : Simple JOIN « Join « SQL / MySQL

1. SQL / MySQL
2. Join
3. Simple JOIN

Self join

  
/*
mysql> SELECT p1.name, p1.sex, p2.name, p2.sex, p1.species
    ->        FROM bird AS p1, bird AS p2
    ->        WHERE p1.species = p2.species AND p1.sex = 'f' AND p2.sex = 'm';
+-----------+------+----------+------+---------+
| name      | sex  | name     | sex  | species |
+-----------+------+----------+------+---------+
| BlueBird1 | f    | RedBird1 | m    | Bus     |
| BlueBird1 | f    | RedBird3 | m    | Bus     |
| BlueBird1 | f    | RedBird4 | m    | Bus     |
+-----------+------+----------+------+---------+
3 rows in set (0.04 sec)


*/
Drop table Bird;

CREATE TABLE Bird (
    name VARCHAR(20), 
    owner VARCHAR(20),
    species VARCHAR(20), 
    sex CHAR(1), 
    birth DATE, 
    death DATE
);
     
INSERT INTO  Bird VALUES ('BlueBird1','Joe','Bus','f','1999-03-30',NULL);
INSERT INTO  Bird VALUES ('RedBird1','Yin','Bus','m','1979-04-30',1998-01-30);
INSERT INTO  Bird VALUES ('BlueBird2','Joe','Car','f','1999-03-30',NULL);
INSERT INTO  Bird VALUES ('RedBird3','Yin','Bus','m','1979-04-30',1998-01-30);
INSERT INTO  Bird VALUES ('RedBird4','Yin','Bus','m','1998-01-30',NULL);
       
  
SELECT p1.name, p1.sex, p2.name, p2.sex, p1.species
       FROM bird AS p1, bird AS p2
       WHERE p1.species = p2.species AND p1.sex = 'f' AND p2.sex = 'm';

           
         
    
  

Related examples in the same category