Mega Code Archive
Pair value vs and operator
<![CDATA[
mysql>
mysql> CREATE TABLE Employee
-> (NAME CHAR(10) NOT NULL,
-> INITIALS CHAR(3) NOT NULL,
-> TOWN VARCHAR(30) NOT NULL,
-> PRIMARY KEY (NAME, INITIALS));
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql> INSERT INTO Employee VALUES ('Link', 'R', 'Stratford');
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO Employee VALUES ('Link', 'P', 'Stratford');
Query OK, 1 row affected (0.00 sec)
mysql>
mysql> INSERT INTO Employee VALUES ('Miller', 'P', 'Douglas');
Query OK, 1 row affected (0.00 sec)
mysql>
mysql> CREATE TABLE PENALTIES
-> (PAYMENTNO INTEGER NOT NULL,
-> NAME CHAR(10) NOT NULL,
-> INITIALS CHAR(3) NOT NULL,
-> AMOUNT DECIMAL(7,2) NOT NULL,
-> PRIMARY KEY (PAYMENTNO),
-> FOREIGN KEY (NAME, INITIALS)
-> REFERENCES Employee (NAME, INITIALS));
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql> INSERT INTO PENALTIES VALUES (1, 'Link', 'R', 100.00);
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO PENALTIES VALUES (2, 'Miller', 'P', 200.00);
Query OK, 1 row affected (0.00 sec)
mysql>
mysql> SELECT NAME, INITIALS, TOWN
-> FROM Employee
-> WHERE NAME IN
-> (SELECT NAME
-> FROM PENALTIES)
-> AND INITIALS IN
-> (SELECT INITIALS
-> FROM PENALTIES);
+--------+----------+-----------+
| NAME | INITIALS | TOWN |
+--------+----------+-----------+
| Link | R | Stratford |
| Link | P | Stratford |
| Miller | P | Douglas |
+--------+----------+-----------+
3 rows in set (0.00 sec)
mysql>
mysql>
mysql> SELECT NAME, INITIALS, TOWN
-> FROM Employee
-> WHERE (NAME, INITIALS) IN
-> (SELECT NAME, INITIALS
-> FROM PENALTIES);
+--------+----------+-----------+
| NAME | INITIALS | TOWN |
+--------+----------+-----------+
| Link | R | Stratford |
| Miller | P | Douglas |
+--------+----------+-----------+
2 rows in set (0.00 sec)
mysql>
mysql>
mysql> SELECT NAME, INITIALS, TOWN
-> FROM Employee
-> WHERE NAME IN
-> (SELECT NAME
-> FROM PENALTIES
-> WHERE Employee.INITIALS =
-> PENALTIES.INITIALS);
+--------+----------+-----------+
| NAME | INITIALS | TOWN |
+--------+----------+-----------+
| Link | R | Stratford |
| Miller | P | Douglas |
+--------+----------+-----------+
2 rows in set (0.00 sec)
mysql>
mysql> drop table Employee;
Query OK, 0 rows affected (0.00 sec)
mysql> drop table PENALTIES;
Query OK, 0 rows affected (0.00 sec)
mysql>
]]>