SQL/MySQL/Select Clause/Sub query
Содержание
- 1 Delete command with sub query
- 2 Sub queries
- 3 Subqueries As Calculated Columns: Simple Subqueries
- 4 Subqueries in the WHERE Clause 2
- 5 Subqueries That Return Multiple Results
- 6 Sub query with ALL command
- 7 Sub query with ANY command
- 8 Sub query with calculation
- 9 Sub query with EXISTS command
- 10 Sub query with IN command
- 11 Sub query with not equal and order
- 12 Sub query with NOT EXISTS command
- 13 Sub query with NOT IN command
- 14 Sub query with string concatenate
- 15 Update command with sub query
Delete command with sub query
<source lang="sql">
Drop table Articles; Drop table Authors; Drop table AuthorArticle; Drop table ArticleOrders;
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);
CREATE TABLE ArticleOrders (
OrderID SMALLINT NOT NULL, ArticleID SMALLINT NOT NULL, Quantity SMALLINT NOT NULL, PRIMARY KEY (OrderID, ArticleID), FOREIGN KEY (ArticleID) REFERENCES Articles (ArticleID)
) ENGINE=INNODB;
INSERT INTO ArticleOrders VALUES (101, 13331, 1),
(101, 12786, 1),
(101, 16284, 2),
(102, 19354, 1),
(102, 15729, 3),
(103, 12786, 2),
(103, 19264, 1),
(103, 13331, 1),
(103, 14356, 2),
(104, 19354, 1),
(105, 15729, 1),
(105, 14356, 2),
(106, 16284, 2),
(106, 13331, 1),
(107, 12786, 3),
(108, 19354, 1),
(108, 16284, 4),
(109, 15729, 1),
(110, 13331, 2),
(110, 12786, 2),
(110, 14356, 2),
(111, 14356, 2);
DELETE ab, b FROM AuthorArticle AS ab, Articles AS b WHERE ab.ArticleID=b.ArticleID
AND ab.AuthID=(SELECT AuthID FROM Authors WHERE AuthorLastName="Yin");
</source>
Sub queries
<source lang="sql">
/* mysql> SELECT CONCAT_WS(" ", AuthorFirstName, AuthorMiddleName, AuthorLastName) AS Author
-> FROM Authors -> WHERE AuthID= -> ( -> SELECT ab.AuthID -> FROM AuthorArticle AS ab, Articles AS b -> WHERE ab.ArticleID=b.ArticleID AND ArticleTitle="AI" -> );
+-------------+ | Author | +-------------+ | Annie Watts | +-------------+ 1 row 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 CONCAT_WS(" ", AuthorFirstName, AuthorMiddleName, AuthorLastName) AS Author FROM Authors WHERE AuthID=
(
SELECT ab.AuthID
FROM AuthorArticle AS ab, Articles AS b
WHERE ab.ArticleID=b.ArticleID AND ArticleTitle="AI"
);
</source>
Subqueries As Calculated Columns: Simple Subqueries
<source lang="sql">
/* mysql> SELECT StudentID, Name,
-> (SELECT COUNT(*) FROM StudentExam -> WHERE StudentExam.StudentID = Student.StudentID) -> AS ExamsTaken -> FROM Student -> ORDER BY ExamsTaken DESC;
+-----------+-------------+------------+ | StudentID | Name | ExamsTaken | +-----------+-------------+------------+ | 1 | Joe Wang | 2 | | 2 | Cory But | 1 | | 3 | JJ Harvests | 0 | +-----------+-------------+------------+ 3 rows in set (0.00 sec)
- /
/* Create Student and StudentExam TABLE */ Drop TABLE Student; Drop TABLE StudentExam; CREATE TABLE Student (
StudentID INT NOT NULL PRIMARY KEY, Name VARCHAR(50) NOT NULL
)TYPE = InnoDB; CREATE TABLE StudentExam (
StudentID INT NOT NULL,
Mark INT,
Comments VARCHAR(255),
CONSTRAINT FK_Student FOREIGN KEY (StudentID)
REFERENCES Student(StudentID)
)TYPE = InnoDB;
/* Insert Data*/ INSERT INTO Student (StudentID,Name) VALUES (1,"John Jones"); INSERT INTO Student (StudentID,Name) VALUES (2,"Gary Burton"); INSERT INTO Student (StudentID,Name) VALUES (3,"Emily Scarlett"); INSERT INTO StudentExam (StudentID,Mark,Comments) VALUES (1,55,"Java"); INSERT INTO StudentExam (StudentID,Mark,Comments) VALUES (1,73,"C#"); INSERT INTO StudentExam (StudentID,Mark,Comments) VALUES (2,44,"JavaScript");
/* Real command */ SELECT StudentID, Name,
(SELECT COUNT(*) FROM StudentExam WHERE StudentExam.StudentID = Student.StudentID) AS ExamsTaken
FROM Student ORDER BY ExamsTaken DESC;
</source>
Subqueries in the WHERE Clause 2
<source lang="sql">
/* mysql> select * from Course; +----------+----------------------+---------+ | CourseID | Name | Credits | +----------+----------------------+---------+ | 1 | Mediaeval Romanian | 5 | | 2 | Philosophy | 5 | | 3 | History of Computing | 5 | +----------+----------------------+---------+ 3 rows in set (0.01 sec) mysql> SELECT Name FROM Course
-> WHERE CourseID = -> ( -> SELECT CourseID from EXAM -> WHERE SustainedOn="10-MAR-03" -> );
Empty set, 1 warning (0.00 sec)
- /
Drop TABLE Course; Drop TABLE Exam;
CREATE TABLE Course (
CourseID INT NOT NULL PRIMARY KEY, Name VARCHAR(50), Credits INT)
TYPE = InnoDB; CREATE TABLE Exam (
ExamID INT NOT NULL PRIMARY KEY, CourseID INT NOT NULL, SustainedOn DATE, Comments VARCHAR(255), INDEX examcourse_index(CourseID)
)TYPE = InnoDB;
INSERT INTO Course (CourseID,Name,Credits) VALUES (1,"Mediaeval Romanian",5); INSERT INTO Course (CourseID,Name,Credits) VALUES (2,"Philosophy",5); INSERT INTO Course (CourseID,Name,Credits) VALUES (3,"History of Computing",5); INSERT INTO Exam (ExamID,CourseID,SustainedOn,Comments) VALUES
(1,1,"2003-03-12","JavaScript");
INSERT INTO Exam (ExamID,CourseID,SustainedOn,Comments) VALUES
(2,1,"2003-03-13","Java");
INSERT INTO Exam (ExamID,CourseID,SustainedOn,Comments) VALUES
(3,2,"2003-03-11","Python");
INSERT INTO Exam (ExamID,CourseID,SustainedOn) VALUES
(4,3,"2003-03-18","Swing");
select * from Course; SELECT Name FROM Course WHERE CourseID = ( SELECT CourseID from EXAM WHERE SustainedOn="10-MAR-03" );
</source>
Subqueries That Return Multiple Results
<source lang="sql">
/* mysql> select * from Course; +----------+----------------------+---------+ | CourseID | Name | Credits | +----------+----------------------+---------+ | 1 | Mediaeval Romanian | 5 | | 2 | Philosophy | 5 | | 3 | History of Computing | 5 | +----------+----------------------+---------+ 3 rows in set (0.00 sec) mysql> SELECT Name FROM Course
-> WHERE CourseID IN -> ( -> SELECT CourseID from EXAM -> WHERE SustainedOn="26-MAR-03" -> );
Empty set, 1 warning (0.00 sec)
- /
Drop TABLE Course; Drop TABLE Exam;
CREATE TABLE Course (
CourseID INT NOT NULL PRIMARY KEY, Name VARCHAR(50), Credits INT)
TYPE = InnoDB; CREATE TABLE Exam (
ExamID INT NOT NULL PRIMARY KEY, CourseID INT NOT NULL, SustainedOn DATE, Comments VARCHAR(255), INDEX examcourse_index(CourseID)
)TYPE = InnoDB;
INSERT INTO Course (CourseID,Name,Credits) VALUES (1,"Mediaeval Romanian",5); INSERT INTO Course (CourseID,Name,Credits) VALUES (2,"Philosophy",5); INSERT INTO Course (CourseID,Name,Credits) VALUES (3,"History of Computing",5); INSERT INTO Exam (ExamID,CourseID,SustainedOn,Comments) VALUES
(1,1,"2003-03-12","JavaScript");
INSERT INTO Exam (ExamID,CourseID,SustainedOn,Comments) VALUES
(2,1,"2003-03-13","Java");
INSERT INTO Exam (ExamID,CourseID,SustainedOn,Comments) VALUES
(3,2,"2003-03-11","Python");
INSERT INTO Exam (ExamID,CourseID,SustainedOn) VALUES
(4,3,"2003-03-18","Swing");
select * from Course;
SELECT Name FROM Course WHERE CourseID IN ( SELECT CourseID from EXAM WHERE SustainedOn="26-MAR-03" );
</source>
Sub query with ALL command
<source lang="sql">
/* mysql> SELECT ArticleTitle, Copyright
-> FROM Articles -> WHERE Copyright > ALL -> ( -> SELECT b.copyright -> FROM Articles AS b JOIN AuthorArticle AS ab USING (ArticleID) -> JOIN Authors AS a USING (AuthID) -> WHERE AuthorLastName="Yin" -> ) -> ORDER BY ArticleTitle;
Empty 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 FROM Articles WHERE Copyright > ALL
(
SELECT b.copyright
FROM Articles AS b JOIN AuthorArticle AS ab USING (ArticleID)
JOIN Authors AS a USING (AuthID)
WHERE AuthorLastName="Yin"
)
ORDER BY ArticleTitle;
</source>
Sub query with ANY command
<source lang="sql">
/* mysql> SELECT ArticleTitle, Copyright
-> FROM Articles -> WHERE Copyright > ANY -> ( -> SELECT b.copyright -> FROM Articles AS b JOIN AuthorArticle AS ab USING (ArticleID) -> JOIN Authors AS a USING (AuthID) -> WHERE AuthorLastName="Yin" -> ) -> ORDER BY ArticleTitle;
Empty 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 FROM Articles WHERE Copyright > ANY
(
SELECT b.copyright
FROM Articles AS b JOIN AuthorArticle AS ab USING (ArticleID)
JOIN Authors AS a USING (AuthID)
WHERE AuthorLastName="Yin"
)
ORDER BY ArticleTitle;
</source>
Sub query with calculation
<source lang="sql">
/* mysql> SELECT ArticleTitle, Copyright
-> FROM Articles -> WHERE Copyright<(SELECT MAX(Copyright)-50 FROM Articles) -> ORDER BY ArticleTitle;
+-------------------+-----------+ | ArticleTitle | Copyright | +-------------------+-----------+ | Buy a paper | 1932 | | How write a paper | 1934 | | Publish a paper | 1919 | +-------------------+-----------+ 3 rows in set (0.05 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 FROM Articles WHERE Copyright<(SELECT MAX(Copyright)-50 FROM Articles) ORDER BY ArticleTitle;
</source>
Sub query with EXISTS command
<source lang="sql">
/* mysql> SELECT ArticleID, ArticleTitle
-> FROM Articles AS b -> WHERE EXISTS -> ( -> SELECT ArticleID -> FROM AuthorArticle AS ab -> WHERE b.ArticleID=ab.ArticleID -> ) -> ORDER BY ArticleTitle;
+-----------+-------------------+ | ArticleID | ArticleTitle | +-----------+-------------------+ | 19354 | AI | | 15729 | Buy a paper | | 16284 | Conferences | | 12786 | How write a paper | | 19264 | Information | | 17695 | Journal | | 14356 | Sell a paper | +-----------+-------------------+ 7 rows in set (0.01 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 ArticleID, ArticleTitle FROM Articles AS b WHERE EXISTS
(
SELECT ArticleID
FROM AuthorArticle AS ab
WHERE b.ArticleID=ab.ArticleID
)
ORDER BY ArticleTitle;
</source>
Sub query with IN command
<source lang="sql">
/* mysql> SELECT ArticleTitle, Copyright
-> FROM Articles -> WHERE Copyright IN -> ( -> SELECT b.copyright -> FROM Articles AS b JOIN AuthorArticle AS ab USING (ArticleID) -> JOIN Authors AS a USING (AuthID) -> WHERE AuthorLastName="Yin" -> ) -> ORDER BY ArticleTitle;
+--------------+-----------+ | ArticleTitle | Copyright | +--------------+-----------+ | Conferences | 1996 | +--------------+-----------+ 1 row in set (0.00 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 FROM Articles WHERE Copyright IN
(
SELECT b.copyright
FROM Articles AS b JOIN AuthorArticle AS ab USING (ArticleID)
JOIN Authors AS a USING (AuthID)
WHERE AuthorLastName="Yin"
)
ORDER BY ArticleTitle;
</source>
Sub query with not equal and order
<source lang="sql">
/* mysql> SELECT DISTINCT CONCAT_WS(" ", AuthorFirstName, AuthorMiddleName, AuthorL astName) AS Author
-> FROM Authors -> WHERE AuthID <> -> ( -> SELECT ab.AuthID -> FROM AuthorArticle AS ab, Articles AS b -> WHERE ab.ArticleID=b.ArticleID AND ArticleTitle="AI" -> ) -> ORDER BY AuthorLastName;
+-------------------+ | Author | +-------------------+ | James Elk | | Jack K Ken | | Mary G. Lee | | Jason Carol Oak | | Tom M Ride | | Henry S. Thompson | | Alan Wang | | Nelson Yin | +-------------------+ 8 rows in set (0.16 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 DISTINCT CONCAT_WS(" ", AuthorFirstName, AuthorMiddleName, AuthorLastName) AS Author FROM Authors WHERE AuthID <>
(
SELECT ab.AuthID
FROM AuthorArticle AS ab, Articles AS b
WHERE ab.ArticleID=b.ArticleID AND ArticleTitle="AI"
)
ORDER BY AuthorLastName;
</source>
Sub query with NOT EXISTS command
<source lang="sql">
/* mysql> SELECT ArticleID, ArticleTitle
-> FROM Articles AS b -> WHERE NOT EXISTS -> ( -> SELECT ArticleID -> FROM AuthorArticle AS ab -> WHERE b.ArticleID=ab.ArticleID -> ) -> ORDER BY ArticleTitle;
+-----------+-----------------+ | ArticleID | ArticleTitle | +-----------+-----------------+ | 13331 | Publish a paper | +-----------+-----------------+ 1 row in set (0.00 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 ArticleID, ArticleTitle FROM Articles AS b WHERE NOT EXISTS
(
SELECT ArticleID
FROM AuthorArticle AS ab
WHERE b.ArticleID=ab.ArticleID
)
ORDER BY ArticleTitle;
</source>
Sub query with NOT IN command
<source lang="sql">
/* mysql> SELECT ArticleTitle, Copyright
-> FROM Articles -> WHERE Copyright NOT IN -> ( -> SELECT b.copyright -> FROM Articles AS b JOIN AuthorArticle AS ab USING (ArticleID) -> JOIN Authors AS a USING (AuthID) -> WHERE AuthorLastName="Yin" -> ) -> ORDER BY ArticleTitle;
+-------------------+-----------+ | ArticleTitle | Copyright | +-------------------+-----------+ | AI | 1993 | | Buy a paper | 1932 | | How write a paper | 1934 | | Information | 1992 | | Journal | 1980 | | Publish a paper | 1919 | | Sell a paper | 1966 | +-------------------+-----------+ 7 rows in set (0.01 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 FROM Articles WHERE Copyright NOT IN
(
SELECT b.copyright
FROM Articles AS b JOIN AuthorArticle AS ab USING (ArticleID)
JOIN Authors AS a USING (AuthID)
WHERE AuthorLastName="Yin"
)
ORDER BY ArticleTitle;
</source>
Sub query with string concatenate
<source lang="sql">
/* mysql> SELECT CONCAT_WS(" ", AuthorFirstName, AuthorMiddleName, AuthorLastName) AS Author
-> FROM Authors -> WHERE AuthID= -> ( -> SELECT ab.AuthID -> FROM AuthorArticle AS ab, Articles AS b -> WHERE ab.ArticleID=b.ArticleID AND ArticleTitle="AI" -> );
+-------------+ | Author | +-------------+ | Annie Watts | +-------------+ 1 row in set (0.01 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 CONCAT_WS(" ", AuthorFirstName, AuthorMiddleName, AuthorLastName) AS Author FROM Authors WHERE AuthID=
(
SELECT ab.AuthID
FROM AuthorArticle AS ab, Articles AS b
WHERE ab.ArticleID=b.ArticleID AND ArticleTitle="AI"
);
</source>
Update command with sub query
<source lang="sql">
/* mysql> UPDATE Articles
-> SET ArticleTitle="The Way of Zen", Copyright=1957 -> WHERE ArticleID= -> ( -> SELECT ab.ArticleID -> FROM Authors AS a, AuthorArticle AS ab -> WHERE a.AuthID=ab.AuthID AND a.AuthorLastName="Yin" -> );
Query OK, 1 row affected (0.02 sec) Rows matched: 1 Changed: 1 Warnings: 0
- /
Drop table Articles; Drop table Authors; Drop table AuthorArticle; Drop table ArticleOrders;
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);
CREATE TABLE ArticleOrders (
OrderID SMALLINT NOT NULL, ArticleID SMALLINT NOT NULL, Quantity SMALLINT NOT NULL, PRIMARY KEY (OrderID, ArticleID), FOREIGN KEY (ArticleID) REFERENCES Articles (ArticleID)
) ENGINE=INNODB;
INSERT INTO ArticleOrders VALUES (101, 13331, 1),
(101, 12786, 1),
(101, 16284, 2),
(102, 19354, 1),
(102, 15729, 3),
(103, 12786, 2),
(103, 19264, 1),
(103, 13331, 1),
(103, 14356, 2),
(104, 19354, 1),
(105, 15729, 1),
(105, 14356, 2),
(106, 16284, 2),
(106, 13331, 1),
(107, 12786, 3),
(108, 19354, 1),
(108, 16284, 4),
(109, 15729, 1),
(110, 13331, 2),
(110, 12786, 2),
(110, 14356, 2),
(111, 14356, 2);
UPDATE Articles SET ArticleTitle="The Way of Zen", Copyright=1957 WHERE ArticleID=
(
SELECT ab.ArticleID
FROM Authors AS a, AuthorArticle AS ab
WHERE a.AuthID=ab.AuthID AND a.AuthorLastName="Yin"
);
</source>
