SQL/MySQL/Join/Simple JOIN
Содержание
Join three tables
/*
mysql> SELECT ArticleTitle, Copyright, CONCAT_WS(" ", AuthorFirstName, AuthorMid
dleName, AuthorLastName) AS Author
-> FROM Articles AS b, AuthorArticle AS ab, Authors AS a
-> WHERE b.ArticleID=ab.ArticleID AND ab.AuthID=a.AuthID AND Copyright<1980
-> ORDER BY ArticleTitle;
+-------------------+-----------+-------------------+
| ArticleTitle | Copyright | Author |
+-------------------+-----------+-------------------+
| Buy a paper | 1932 | James Elk |
| Buy a paper | 1932 | Mary G. Lee |
| How write a paper | 1934 | Tom M Ride |
| Sell a paper | 1966 | Henry S. Thompson |
+-------------------+-----------+-------------------+
4 rows in set (0.00 sec)
mysql>
*/
Drop table Articles;
Drop table Authors;
Drop table AuthorArticle;
CREATE TABLE Articles (
ArticleID SMALLINT NOT NULL PRIMARY KEY,
ArticleTitle VARCHAR(60) NOT NULL,
Copyright YEAR NOT NULL
)
ENGINE=INNODB;
INSERT INTO Articles VALUES (12786, "How write a paper", 1934),
(13331, "Publish a paper", 1919),
(14356, "Sell a paper", 1966),
(15729, "Buy a paper", 1932),
(16284, "Conferences", 1996),
(17695, "Journal", 1980),
(19264, "Information", 1992),
(19354, "AI", 1993);
CREATE TABLE Authors (
AuthID SMALLINT NOT NULL PRIMARY KEY,
AuthorFirstName VARCHAR(20),
AuthorMiddleName VARCHAR(20),
AuthorLastName VARCHAR(20)
)
ENGINE=INNODB;
INSERT INTO Authors VALUES (1006, "Henry", "S.", "Thompson"),
(1007, "Jason", "Carol", "Oak"),
(1008, "James", NULL, "Elk"),
(1009, "Tom", "M", "Ride"),
(1010, "Jack", "K", "Ken"),
(1011, "Mary", "G.", "Lee"),
(1012, "Annie", NULL, "Peng"),
(1013, "Alan", NULL, "Wang"),
(1014, "Nelson", NULL, "Yin");
CREATE TABLE AuthorArticle (
AuthID SMALLINT NOT NULL,
ArticleID SMALLINT NOT NULL,
PRIMARY KEY (AuthID, ArticleID),
FOREIGN KEY (AuthID) REFERENCES Authors (AuthID),
FOREIGN KEY (ArticleID) REFERENCES Articles (ArticleID)
)
ENGINE=INNODB;
INSERT INTO AuthorArticle VALUES (1006, 14356),
(1008, 15729),
(1009, 12786),
(1010, 17695),
(1011, 15729),
(1012, 19264),
(1012, 19354),
(1014, 16284);
SELECT ArticleTitle, Copyright, CONCAT_WS(" ", AuthorFirstName, AuthorMiddleName, AuthorLastName) AS Author
FROM Articles AS b, AuthorArticle AS ab, Authors AS a
WHERE b.ArticleID=ab.ArticleID AND ab.AuthID=a.AuthID AND Copyright<1980
ORDER BY ArticleTitle;
JOIN two tables with alias name
/*
mysql> SELECT ArticleTitle, Copyright, ab.AuthID
-> FROM Articles AS b JOIN AuthorArticle AS ab
-> ON b.ArticleID=ab.ArticleID
-> ORDER BY ArticleTitle;
+-------------------+-----------+--------+
| ArticleTitle | Copyright | AuthID |
+-------------------+-----------+--------+
| AI | 1993 | 1012 |
| Buy a paper | 1932 | 1008 |
| Buy a paper | 1932 | 1011 |
| Conferences | 1996 | 1014 |
| How write a paper | 1934 | 1009 |
| Information | 1992 | 1012 |
| Journal | 1980 | 1010 |
| Sell a paper | 1966 | 1006 |
+-------------------+-----------+--------+
8 rows in set (0.04 sec)
*/
Drop table Articles;
Drop table Authors;
Drop table AuthorArticle;
CREATE TABLE Articles (
ArticleID SMALLINT NOT NULL PRIMARY KEY,
ArticleTitle VARCHAR(60) NOT NULL,
Copyright YEAR NOT NULL
)
ENGINE=INNODB;
INSERT INTO Articles VALUES (12786, "How write a paper", 1934),
(13331, "Publish a paper", 1919),
(14356, "Sell a paper", 1966),
(15729, "Buy a paper", 1932),
(16284, "Conferences", 1996),
(17695, "Journal", 1980),
(19264, "Information", 1992),
(19354, "AI", 1993);
CREATE TABLE Authors (
AuthID SMALLINT NOT NULL PRIMARY KEY,
AuthorFirstName VARCHAR(20),
AuthorMiddleName VARCHAR(20),
AuthorLastName VARCHAR(20)
)
ENGINE=INNODB;
INSERT INTO Authors VALUES (1006, "Henry", "S.", "Thompson"),
(1007, "Jason", "Carol", "Oak"),
(1008, "James", NULL, "Elk"),
(1009, "Tom", "M", "Ride"),
(1010, "Jack", "K", "Ken"),
(1011, "Mary", "G.", "Lee"),
(1012, "Annie", NULL, "Peng"),
(1013, "Alan", NULL, "Wang"),
(1014, "Nelson", NULL, "Yin");
CREATE TABLE AuthorArticle (
AuthID SMALLINT NOT NULL,
ArticleID SMALLINT NOT NULL,
PRIMARY KEY (AuthID, ArticleID),
FOREIGN KEY (AuthID) REFERENCES Authors (AuthID),
FOREIGN KEY (ArticleID) REFERENCES Articles (ArticleID)
)
ENGINE=INNODB;
INSERT INTO AuthorArticle VALUES (1006, 14356),
(1008, 15729),
(1009, 12786),
(1010, 17695),
(1011, 15729),
(1012, 19264),
(1012, 19354),
(1014, 16284);
SELECT ArticleTitle, Copyright, ab.AuthID
FROM Articles AS b JOIN AuthorArticle AS ab
ON b.ArticleID=ab.ArticleID
ORDER BY ArticleTitle;
Query data from two tables
/*
mysql> SELECT ArticleTitle, AuthID FROM Articles, AuthorArticle;
+-------------------+--------+
| ArticleTitle | AuthID |
+-------------------+--------+
| How write a paper | 1006 |
| Publish a paper | 1006 |
| Sell a paper | 1006 |
| Buy a paper | 1006 |
| Conferences | 1006 |
| Journal | 1006 |
| Information | 1006 |
| AI | 1006 |
| How write a paper | 1008 |
| Publish a paper | 1008 |
| Sell a paper | 1008 |
| Buy a paper | 1008 |
| Conferences | 1008 |
| Journal | 1008 |
| Information | 1008 |
| AI | 1008 |
| How write a paper | 1009 |
| Publish a paper | 1009 |
| Sell a paper | 1009 |
| Buy a paper | 1009 |
| Conferences | 1009 |
| Journal | 1009 |
| Information | 1009 |
| AI | 1009 |
| How write a paper | 1010 |
| Publish a paper | 1010 |
| Sell a paper | 1010 |
| Buy a paper | 1010 |
| Conferences | 1010 |
| Journal | 1010 |
| Information | 1010 |
| AI | 1010 |
| How write a paper | 1011 |
| Publish a paper | 1011 |
| Sell a paper | 1011 |
| Buy a paper | 1011 |
| Conferences | 1011 |
| Journal | 1011 |
| Information | 1011 |
| AI | 1011 |
| How write a paper | 1012 |
| Publish a paper | 1012 |
| Sell a paper | 1012 |
| Buy a paper | 1012 |
| Conferences | 1012 |
| Journal | 1012 |
| Information | 1012 |
| AI | 1012 |
| How write a paper | 1012 |
| Publish a paper | 1012 |
| Sell a paper | 1012 |
| Buy a paper | 1012 |
| Conferences | 1012 |
| Journal | 1012 |
| Information | 1012 |
| AI | 1012 |
| How write a paper | 1014 |
| Publish a paper | 1014 |
| Sell a paper | 1014 |
| Buy a paper | 1014 |
| Conferences | 1014 |
| Journal | 1014 |
| Information | 1014 |
| AI | 1014 |
+-------------------+--------+
64 rows in set (0.01 sec)
mysql>
*/
Drop table Articles;
Drop table Authors;
Drop table AuthorArticle;
CREATE TABLE Articles (
ArticleID SMALLINT NOT NULL PRIMARY KEY,
ArticleTitle VARCHAR(60) NOT NULL,
Copyright YEAR NOT NULL
)
ENGINE=INNODB;
INSERT INTO Articles VALUES (12786, "How write a paper", 1934),
(13331, "Publish a paper", 1919),
(14356, "Sell a paper", 1966),
(15729, "Buy a paper", 1932),
(16284, "Conferences", 1996),
(17695, "Journal", 1980),
(19264, "Information", 1992),
(19354, "AI", 1993);
CREATE TABLE Authors (
AuthID SMALLINT NOT NULL PRIMARY KEY,
AuthorFirstName VARCHAR(20),
AuthorMiddleName VARCHAR(20),
AuthorLastName VARCHAR(20)
)
ENGINE=INNODB;
INSERT INTO Authors VALUES (1006, "Henry", "S.", "Thompson"),
(1007, "Jason", "Carol", "Oak"),
(1008, "James", NULL, "Elk"),
(1009, "Tom", "M", "Ride"),
(1010, "Jack", "K", "Ken"),
(1011, "Mary", "G.", "Lee"),
(1012, "Annie", NULL, "Peng"),
(1013, "Alan", NULL, "Wang"),
(1014, "Nelson", NULL, "Yin");
CREATE TABLE AuthorArticle (
AuthID SMALLINT NOT NULL,
ArticleID SMALLINT NOT NULL,
PRIMARY KEY (AuthID, ArticleID),
FOREIGN KEY (AuthID) REFERENCES Authors (AuthID),
FOREIGN KEY (ArticleID) REFERENCES Articles (ArticleID)
)
ENGINE=INNODB;
INSERT INTO AuthorArticle VALUES (1006, 14356),
(1008, 15729),
(1009, 12786),
(1010, 17695),
(1011, 15729),
(1012, 19264),
(1012, 19354),
(1014, 16284);
SELECT ArticleTitle, AuthID FROM Articles, AuthorArticle;
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";
Simple table join
/*
mysql> CREATE TABLE Articles (
-> ArticleID SMALLINT NOT NULL PRIMARY KEY,
-> ArticleTitle VARCHAR(60) NOT NULL,
-> Copyright YEAR NOT NULL
-> )
-> ENGINE=INNODB;
Query OK, 0 rows affected (0.13 sec)
mysql> INSERT INTO Articles VALUES (12786, "How write a paper", 1934),
-> (13331, "Publish a paper", 1919),
-> (14356, "Sell a paper", 1966),
-> (15729, "Buy a paper", 1932),
-> (16284, "Conferences", 1996),
-> (17695, "Journal", 1980),
-> (19264, "Information", 1992),
-> (19354, "AI", 1993);
Query OK, 8 rows affected (0.08 sec)
Records: 8 Duplicates: 0 Warnings: 0
mysql> CREATE TABLE Authors (
-> AuthID SMALLINT NOT NULL PRIMARY KEY,
-> AuthorFirstName VARCHAR(20),
-> AuthorMiddleName VARCHAR(20),
-> AuthorLastName VARCHAR(20)
-> )
-> ENGINE=INNODB;
Query OK, 0 rows affected (0.16 sec)
mysql> INSERT INTO Authors VALUES (1006, "Henry", "S.", "Thompson"),
-> (1007, "Jason", "Carol", "Oak"),
-> (1008, "James", NULL, "Elk"),
-> (1009, "Tom", "M", "Ride"),
-> (1010, "Jack", "K", "Ken"),
-> (1011, "Mary", "G.", "Lee"),
-> (1012, "Annie", NULL, "Peng"),
-> (1013, "Alan", NULL, "Wang"),
-> (1014, "Nelson", NULL, "Yin");
Query OK, 9 rows affected (0.03 sec)
Records: 9 Duplicates: 0 Warnings: 0
mysql> CREATE TABLE AuthorArticle (
-> AuthID SMALLINT NOT NULL,
-> ArticleID SMALLINT NOT NULL,
-> PRIMARY KEY (AuthID, ArticleID),
-> FOREIGN KEY (AuthID) REFERENCES Authors (AuthID),
-> FOREIGN KEY (ArticleID) REFERENCES Articles (ArticleID)
-> )
-> ENGINE=INNODB;
Query OK, 0 rows affected (0.23 sec)
mysql> INSERT INTO AuthorArticle VALUES (1006, 14356),
-> (1008, 15729),
-> (1009, 12786),
-> (1010, 17695),
-> (1011, 15729), (1012, 19264),
-> (1012, 19354),
-> (1014, 16284);
Query OK, 8 rows affected (0.07 sec)
Records: 8 Duplicates: 0 Warnings: 0
mysql> SELECT ArticleTitle, Copyright, AuthID
-> FROM Articles AS b, AuthorArticle AS ab
-> WHERE b.ArticleID=ab.ArticleID
-> ORDER BY ArticleTitle;
+-------------------+-----------+--------+
| ArticleTitle | Copyright | AuthID |
+-------------------+-----------+--------+
| AI | 1993 | 1012 |
| Buy a paper | 1932 | 1008 |
| Buy a paper | 1932 | 1011 |
| Conferences | 1996 | 1014 |
| How write a paper | 1934 | 1009 |
| Information | 1992 | 1012 |
| Journal | 1980 | 1010 |
| Sell a paper | 1966 | 1006 |
+-------------------+-----------+--------+
8 rows in set (0.02 sec)
*/
Drop table Articles;
Drop table Authors;
Drop table AuthorArticle;
CREATE TABLE Articles (
ArticleID SMALLINT NOT NULL PRIMARY KEY,
ArticleTitle VARCHAR(60) NOT NULL,
Copyright YEAR NOT NULL
)
ENGINE=INNODB;
INSERT INTO Articles VALUES (12786, "How write a paper", 1934),
(13331, "Publish a paper", 1919),
(14356, "Sell a paper", 1966),
(15729, "Buy a paper", 1932),
(16284, "Conferences", 1996),
(17695, "Journal", 1980),
(19264, "Information", 1992),
(19354, "AI", 1993);
CREATE TABLE Authors (
AuthID SMALLINT NOT NULL PRIMARY KEY,
AuthorFirstName VARCHAR(20),
AuthorMiddleName VARCHAR(20),
AuthorLastName VARCHAR(20)
)
ENGINE=INNODB;
INSERT INTO Authors VALUES (1006, "Henry", "S.", "Thompson"),
(1007, "Jason", "Carol", "Oak"),
(1008, "James", NULL, "Elk"),
(1009, "Tom", "M", "Ride"),
(1010, "Jack", "K", "Ken"),
(1011, "Mary", "G.", "Lee"),
(1012, "Annie", NULL, "Peng"),
(1013, "Alan", NULL, "Wang"),
(1014, "Nelson", NULL, "Yin");
CREATE TABLE AuthorArticle (
AuthID SMALLINT NOT NULL,
ArticleID SMALLINT NOT NULL,
PRIMARY KEY (AuthID, ArticleID),
FOREIGN KEY (AuthID) REFERENCES Authors (AuthID),
FOREIGN KEY (ArticleID) REFERENCES Articles (ArticleID)
)
ENGINE=INNODB;
INSERT INTO AuthorArticle VALUES (1006, 14356),
(1008, 15729),
(1009, 12786),
(1010, 17695),
(1011, 15729),
(1012, 19264),
(1012, 19354),
(1014, 16284);
SELECT ArticleTitle, Copyright, AuthID
FROM Articles AS b, AuthorArticle AS ab
WHERE b.ArticleID=ab.ArticleID
ORDER BY ArticleTitle;
Using More Than one Table
/*
mysql> SELECT bird.name,
-> (YEAR(date)-YEAR(birth)) - (RIGHT(date,5)<RIGHT(birth,5)) AS age,
-> remark
-> FROM bird, event
-> WHERE bird.name = event.name AND event.type = "sell";
+---------+------+----------------+
| name | age | remark |
+---------+------+----------------+
| RedBird | 16 | sell just sell |
+---------+------+----------------+
1 row in set (0.00 sec)
*/
/* Create table */
Drop table event;
Drop table Bird;
CREATE TABLE Bird (
name VARCHAR(20),
owner VARCHAR(20),
species VARCHAR(20),
sex CHAR(1),
birth DATE,
death DATE
);
CREATE TABLE event (
name VARCHAR(20),
date DATE,
type VARCHAR(15),
remark VARCHAR(255)
);
INSERT INTO Bird VALUES ("BlueBird","Joe","Car","f","1999-03-30",NULL);
INSERT INTO Bird VALUES ("RedBird","Yin","Bus","m","1979-04-30",1998-01-30);
INSERT INTO Bird VALUES ("RedBird1","Yin","Bus","m","1998-01-30",NULL);
insert into event values ("BlueBird","1995-05-15","buy","Buy just Buy");
insert into event values ("RedBird","1995-05-15","sell","sell just sell");
insert into event values ("RedBird2","1995-05-15","sell","sell and buy");
SELECT bird.name,
(YEAR(date)-YEAR(birth)) - (RIGHT(date,5)<RIGHT(birth,5)) AS age,
remark
FROM bird, event
WHERE bird.name = event.name AND event.type = "sell";