Pick a single row from the full join : Full Join « Join « SQL / MySQL

1. SQL / MySQL
2. Join
3. Full Join

Pick a single row from the full join

    
mysql>
mysql> CREATE TABLE shirt (item CHAR(20));
Query OK, 0 rows affected (0.01 sec)

mysql> INSERT INTO shirt (item) VALUES('Pinstripe'),('Tie-Dye'),('Black');
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql>
mysql> CREATE TABLE tie (item CHAR(20));
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO tie (item) VALUES('Fleur de lis'),('Paisley'),('Polka Dot');
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql>
mysql> SELECT * FROM shirt, tie;
+-----------+--------------+
| item      | item         |
+-----------+--------------+
| Pinstripe | Fleur de lis |
| Tie-Dye   | Fleur de lis |
| Black     | Fleur de lis |
| Pinstripe | Paisley      |
| Tie-Dye   | Paisley      |
| Black     | Paisley      |
| Pinstripe | Polka Dot    |
| Tie-Dye   | Polka Dot    |
| Black     | Polka Dot    |
+-----------+--------------+
9 rows in set (0.00 sec)

mysql> SELECT * FROM shirt, tie;
+-----------+--------------+
| item      | item         |
+-----------+--------------+
| Pinstripe | Fleur de lis |
| Tie-Dye   | Fleur de lis |
| Black     | Fleur de lis |
| Pinstripe | Paisley      |
| Tie-Dye   | Paisley      |
| Black     | Paisley      |
| Pinstripe | Polka Dot    |
| Tie-Dye   | Polka Dot    |
| Black     | Polka Dot    |
+-----------+--------------+
9 rows in set (0.00 sec)

mysql>
mysql> SELECT shirt.item, tie.item FROM shirt, tie
    -> ORDER BY RAND( ) LIMIT 1;
+-----------+-----------+
| item      | item      |
+-----------+-----------+
| Pinstripe | Polka Dot |
+-----------+-----------+
1 row in set (0.00 sec)

mysql>
mysql> drop table shirt;
Query OK, 0 rows affected (0.00 sec)

mysql> drop table tie;
Query OK, 0 rows affected (0.00 sec)

mysql>

   
    
    
    
  

Related examples in the same category