SQL/MySQL/Select Clause/In — различия между версиями

Материал из SQL эксперт
Перейти к: навигация, поиск
м (1 версия)
 
м (1 версия)
 
(нет различий)

Текущая версия на 10:16, 26 мая 2010

Simple demo for IN

/*
mysql> Drop table report;
Query OK, 0 rows affected (0.00 sec)
mysql> CREATE TABLE report (
    ->        article INT(4),
    ->        dealer  CHAR(20),
    ->        price   DOUBLE(16,2)
    -> );
Query OK, 0 rows affected (0.05 sec)
mysql> INSERT INTO report VALUES (1,"A",4.45),
    ->                         (1,"B",5.45),
    ->                         (2,"A",16.67),
    ->                         (3,"B",6.12),
    ->                         (3,"C",2.78),
    ->                         (3,"D",2.34),
    ->                         (4,"D",21.29);
Query OK, 7 rows affected (0.01 sec)
Records: 7  Duplicates: 0  Warnings: 0
mysql> SELECT * FROM report;
+---------+--------+-------+
| article | dealer | price |
+---------+--------+-------+
|       1 | A      |  4.45 |
|       1 | B      |  5.45 |
|       2 | A      | 16.67 |
|       3 | B      |  6.12 |
|       3 | C      |  2.78 |
|       3 | D      |  2.34 |
|       4 | D      | 21.29 |
+---------+--------+-------+
7 rows in set (0.01 sec)
mysql> SELECT *
    -> FROM report
    -> WHERE dealer IN("A","C","D");
+---------+--------+-------+
| article | dealer | price |
+---------+--------+-------+
|       1 | A      |  4.45 |
|       2 | A      | 16.67 |
|       3 | C      |  2.78 |
|       3 | D      |  2.34 |
|       4 | D      | 21.29 |
+---------+--------+-------+
5 rows in set (0.00 sec)

*/
Drop table report;  
CREATE TABLE report (
       article INT(4),
       dealer  CHAR(20),
       price   DOUBLE(16,2)
);
INSERT INTO report VALUES (1,"A",4.45),
                        (1,"B",5.45),
                        (2,"A",16.67),
                        (3,"B",6.12),
                        (3,"C",2.78),
                        (3,"D",2.34),
                        (4,"D",21.29);
    
SELECT * FROM report;    
  
SELECT *
FROM report
WHERE dealer IN("A","C","D");



Simple demo for NOT IN

Drop table employee;
CREATE TABLE employee (
    id int unsigned not null auto_increment primary key,
    firstname varchar(20),
    lastname varchar(20),
    title varchar(30),
    age int,
    yearofservice int,
    salary int,
    perks int,
    email varchar(60)
); 

INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("John", "Chen", "Senior Programmer", 31, 3, 120000, 25000, "j@hotmail.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Jan", "Pillai", "Senior Programmer", 32, 4, 110000, 20000, "g@yahoo.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Ane", "Pandit", "Web Designer", 24, 3, 90000, 15000, "a@gmail.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Mary", "Anchor", "Web Designer", 27, 2, 85000, 15000, "m@mail.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Fred", "King", "Programmer", 32, 3, 75000, 15000, "f@net.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("John", "Mac", "Programmer", 32, 4, 80000, 16000, "j@hotmail.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Arthur", "Sam", "Programmer", 28, 2, 75000, 14000, "e@yahoo.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Alok", "Nanda", "Programmer", 32, 3, 70000, 10000, "a@yahoo.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Susan", "Ra", "Multimedia Programmer", 32, 4, 90000, 15000, "h@gmail.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Paul", "Simon", "Multimedia Programmer", 23, 1, 85000, 12000, "ps@gmail.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Edward", "Parhar", "Multimedia Programmer", 30, 2, 75000, 15000, "a@hotmail.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Kim", "Hunter", "Senior Web Designer", 32, 4, 110000, 20000, "kim@coolmail.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Roger", "Lewis", "System Administrator", 32, 3, 100000, 13000, "roger@mail.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Danny", "Gibson", "System Administrator", 31, 2, 90000, 12000, "danny@hotmail.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Mike", "Harper", "Senior Marketing Executive", 36, 1, 120000, 28000, "m@gmail.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Mary", "Sunday", "Marketing Executive", 31, 5, 90000, 25000, "monica@bigmail.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Jack", "Sim", "Marketing Executive", 27, 1, 70000, 18000, "hal@gmail.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Joe", "Irvine", "Marketing Executive", 27, 1, 72000, 18000, "joseph@hotmail.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Henry", "Ali", "Customer Service Manager", 32, 3, 70000, 9000, "shahida@hotmail.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Peter", "Champion", "Finance Manager", 32, 2, 120000, 25000, "peter@yahoo.ru");

select * from employee;
/* The names and titles of all non programmers are displayed.
*/
SELECT firstname, lastName, title from
       employee where title NOT IN
       ("Programmer", "Senior Programmer",
       "Multimedia Programmer");



Use IN and BETWEEN AND

Drop table employee;
CREATE TABLE employee (
    id int unsigned not null auto_increment primary key,
    firstname varchar(20),
    lastname varchar(20),
    title varchar(30),
    age int,
    yearofservice int,
    salary int,
    perks int,
    email varchar(60)
); 

INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("John", "Chen", "Senior Programmer", 31, 3, 120000, 25000, "j@hotmail.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Jan", "Pillai", "Senior Programmer", 32, 4, 110000, 20000, "g@yahoo.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Ane", "Pandit", "Web Designer", 24, 3, 90000, 15000, "a@gmail.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Mary", "Anchor", "Web Designer", 27, 2, 85000, 15000, "m@mail.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Fred", "King", "Programmer", 32, 3, 75000, 15000, "f@net.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("John", "Mac", "Programmer", 32, 4, 80000, 16000, "j@hotmail.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Arthur", "Sam", "Programmer", 28, 2, 75000, 14000, "e@yahoo.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Alok", "Nanda", "Programmer", 32, 3, 70000, 10000, "a@yahoo.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Susan", "Ra", "Multimedia Programmer", 32, 4, 90000, 15000, "h@gmail.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Paul", "Simon", "Multimedia Programmer", 23, 1, 85000, 12000, "ps@gmail.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Edward", "Parhar", "Multimedia Programmer", 30, 2, 75000, 15000, "a@hotmail.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Kim", "Hunter", "Senior Web Designer", 32, 4, 110000, 20000, "kim@coolmail.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Roger", "Lewis", "System Administrator", 32, 3, 100000, 13000, "roger@mail.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Danny", "Gibson", "System Administrator", 31, 2, 90000, 12000, "danny@hotmail.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Mike", "Harper", "Senior Marketing Executive", 36, 1, 120000, 28000, "m@gmail.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Mary", "Sunday", "Marketing Executive", 31, 5, 90000, 25000, "monica@bigmail.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Jack", "Sim", "Marketing Executive", 27, 1, 70000, 18000, "hal@gmail.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Joe", "Irvine", "Marketing Executive", 27, 1, 72000, 18000, "joseph@hotmail.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Henry", "Ali", "Customer Service Manager", 32, 3, 70000, 9000, "shahida@hotmail.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Peter", "Champion", "Finance Manager", 32, 2, 120000, 25000, "peter@yahoo.ru");
select * from employee;
/* The statement displays all non programmers who are not between the ages 
of 28 and 32.
*/
SELECT firstname, lastName, title, age from
       employee where title NOT IN
       ("Programmer", "Senior Programmer",
       "Multimedia Programmer") AND
       age NOT BETWEEN 28 AND 32;



Use IN and order rows

Drop table employee;
CREATE TABLE employee (
    id int unsigned not null auto_increment primary key,
    firstname varchar(20),
    lastname varchar(20),
    title varchar(30),
    age int,
    yearofservice int,
    salary int,
    perks int,
    email varchar(60)
); 

INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("John", "Chen", "Senior Programmer", 31, 3, 120000, 25000, "j@hotmail.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Jan", "Pillai", "Senior Programmer", 32, 4, 110000, 20000, "g@yahoo.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Ane", "Pandit", "Web Designer", 24, 3, 90000, 15000, "a@gmail.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Mary", "Anchor", "Web Designer", 27, 2, 85000, 15000, "m@mail.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Fred", "King", "Programmer", 32, 3, 75000, 15000, "f@net.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("John", "Mac", "Programmer", 32, 4, 80000, 16000, "j@hotmail.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Arthur", "Sam", "Programmer", 28, 2, 75000, 14000, "e@yahoo.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Alok", "Nanda", "Programmer", 32, 3, 70000, 10000, "a@yahoo.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Susan", "Ra", "Multimedia Programmer", 32, 4, 90000, 15000, "h@gmail.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Paul", "Simon", "Multimedia Programmer", 23, 1, 85000, 12000, "ps@gmail.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Edward", "Parhar", "Multimedia Programmer", 30, 2, 75000, 15000, "a@hotmail.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Kim", "Hunter", "Senior Web Designer", 32, 4, 110000, 20000, "kim@coolmail.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Roger", "Lewis", "System Administrator", 32, 3, 100000, 13000, "roger@mail.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Danny", "Gibson", "System Administrator", 31, 2, 90000, 12000, "danny@hotmail.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Mike", "Harper", "Senior Marketing Executive", 36, 1, 120000, 28000, "m@gmail.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Mary", "Sunday", "Marketing Executive", 31, 5, 90000, 25000, "monica@bigmail.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Jack", "Sim", "Marketing Executive", 27, 1, 70000, 18000, "hal@gmail.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Joe", "Irvine", "Marketing Executive", 27, 1, 72000, 18000, "joseph@hotmail.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Henry", "Ali", "Customer Service Manager", 32, 3, 70000, 9000, "shahida@hotmail.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Peter", "Champion", "Finance Manager", 32, 2, 120000, 25000, "peter@yahoo.ru");
  
select * from employee;
SELECT lastName, firstname from employee
       where title IN ("Programmer",
       "Web Designer") ORDER BY lastName;



Use IN for static values

Drop table sales;
  
CREATE TABLE sales(
    num MEDIUMINT NOT NULL AUTO_INCREMENT,
    name CHAR(20),
    winter INT,
    spring INT,
    summer INT,
    fall INT,
    category CHAR(13),
    primary key(num)
)type=MyISAM;

insert into sales value(1, "Java", 1067 , 200, 150, 267,"Holiday");
insert into sales value(2, "C",970,770,531,486,"Profession");
insert into sales value(3, "JavaScript",53,13,21,856,"Literary");
insert into sales value(4, "SQL",782,357,168,250,"Profession");
insert into sales value(5, "Oracle",589,795,367,284,"Holiday");
insert into sales value(6, "MySQL",953,582,336,489,"Literary");
insert into sales value(7, "Cplus",752,657,259,478,"Literary");
insert into sales value(8, "Python",67,23,83,543,"Holiday");
insert into sales value(9, "PHP",673,48,625,52,"Profession");
select * from sales;

SELECT name AS Name, category AS Category
FROM sales
WHERE category IN("Profession", "Holiday");



Use IN for string value

  Drop table employee;
CREATE TABLE employee (
    id int unsigned not null auto_increment primary key,
    firstname varchar(20),
    lastname varchar(20),
    title varchar(30),
    age int,
    yearofservice int,
    salary int,
    perks int,
    email varchar(60)
); 

INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("John", "Chen", "Senior Programmer", 31, 3, 120000, 25000, "j@hotmail.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Jan", "Pillai", "Senior Programmer", 32, 4, 110000, 20000, "g@yahoo.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Ane", "Pandit", "Web Designer", 24, 3, 90000, 15000, "a@gmail.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Mary", "Anchor", "Web Designer", 27, 2, 85000, 15000, "m@mail.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Fred", "King", "Programmer", 32, 3, 75000, 15000, "f@net.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("John", "Mac", "Programmer", 32, 4, 80000, 16000, "j@hotmail.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Arthur", "Sam", "Programmer", 28, 2, 75000, 14000, "e@yahoo.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Alok", "Nanda", "Programmer", 32, 3, 70000, 10000, "a@yahoo.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Susan", "Ra", "Multimedia Programmer", 32, 4, 90000, 15000, "h@gmail.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Paul", "Simon", "Multimedia Programmer", 23, 1, 85000, 12000, "ps@gmail.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Edward", "Parhar", "Multimedia Programmer", 30, 2, 75000, 15000, "a@hotmail.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Kim", "Hunter", "Senior Web Designer", 32, 4, 110000, 20000, "kim@coolmail.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Roger", "Lewis", "System Administrator", 32, 3, 100000, 13000, "roger@mail.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Danny", "Gibson", "System Administrator", 31, 2, 90000, 12000, "danny@hotmail.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Mike", "Harper", "Senior Marketing Executive", 36, 1, 120000, 28000, "m@gmail.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Mary", "Sunday", "Marketing Executive", 31, 5, 90000, 25000, "monica@bigmail.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Jack", "Sim", "Marketing Executive", 27, 1, 70000, 18000, "hal@gmail.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Joe", "Irvine", "Marketing Executive", 27, 1, 72000, 18000, "joseph@hotmail.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Henry", "Ali", "Customer Service Manager", 32, 3, 70000, 9000, "shahida@hotmail.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Peter", "Champion", "Finance Manager", 32, 2, 120000, 25000, "peter@yahoo.ru");
select * from employee;
 select lastName, firstname, title    
        from employee where
        title IN
        ("Senior Programmer",  
        "Multimedia Programmer");



Use IN in where clause

/*
mysql> Drop table Sale;
Query OK, 0 rows affected (0.00 sec)
mysql> CREATE TABLE Sale
    -> (
    ->    ID SMALLINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    ->    Name VARCHAR(50) NOT NULL,
    ->    InStock SMALLINT UNSIGNED NOT NULL,
    ->    OnOrder SMALLINT UNSIGNED NOT NULL,
    ->    Reserved SMALLINT UNSIGNED NOT NULL,
    ->    Department ENUM("Classical", "Popular") NOT NULL,
    ->    Category VARCHAR(20)
    -> );
Query OK, 0 rows affected (0.05 sec)
mysql> INSERT INTO Sale (Name,      InStock, OnOrder, Reserved, Department, Cate
gory)
    -> VALUES          ("Bloodshot", 11,      6,       1,        "Popular",  "Ro
ck"),
    ->                 ("Opera",     12,      5,       2,        "Classical","Op
era"),
    ->                 ("Jazz",      13,      4,       3,        "Popular",  "Ja
zz"),
    ->                 ("Music",     4,       3,       4,        "Classical","Da
nce"),
    ->                 ("Violin",    25,      2,       5,        "Classical", NU
LL),
    ->                 ("Toscana",   16,      1,       6,        "Classical", NU
LL),
    ->                 ("Blues",     7,       22,      7,        "Popular",   "B
lues"),
    ->                 ("Pure",      38,      5,       11,       "Popular",   NU
LL),
    ->                 ("Mud",       19,      11,      12,       "Popular",  "Co
untry"),
    ->                 ("Essence",   5,       23,      12,       "Popular", "New
 Age"),
    ->                 ("Embrace",   21,      12,      14,       "Popular", "New
 Age"),
    ->                 ("Satie",     42,      17,      15,       "Classical", NU
LL),
    ->                 ("Lake",      23,      47,      28,       "Classical", "D
ance"),
    ->                 ("Favorites", 34,      15,      12,       "Classical", "G
eneral"),
    ->                 ("Boheme",    25,      12,       5,       "Classical", "O
pera"),  ("Cantatas",  26,      13,      8,        "Classical", "General"),
    ->                 ("Road",      27,      13,      17,       "Popular", "Cou
ntry"),
    ->                 ("Paris",     18,      25,      10,       "Popular", "Jaz
z"),
    ->                 ("Woman",     29,      4,        7,       "Popular", "Blu
es"),
    ->                 ("Bach",      21,      13,      16,       "Classical", "G
eneral"),
    ->                 ("Opera",     12,      32,      12,       "Classical", "O
pera"),
    ->                 ("Soul",      13,      30,      14,       "Popular", NULL
),
    ->                 ("Stages",    44,      0,       8,        "Popular", "Blu
es"),
    ->                 ("Bach",      15,      1,       8,        "Classical", "G
eneral");
Query OK, 24 rows affected (0.00 sec)
Records: 24  Duplicates: 0  Warnings: 0
mysql> select * from Sale;
+----+-----------+---------+---------+----------+------------+----------+
| ID | Name      | InStock | OnOrder | Reserved | Department | Category |
+----+-----------+---------+---------+----------+------------+----------+
|  1 | Bloodshot |      11 |       6 |        1 | Popular    | Rock     |
|  2 | Opera     |      12 |       5 |        2 | Classical  | Opera    |
|  3 | Jazz      |      13 |       4 |        3 | Popular    | Jazz     |
|  4 | Music     |       4 |       3 |        4 | Classical  | Dance    |
|  5 | Violin    |      25 |       2 |        5 | Classical  | NULL     |
|  6 | Toscana   |      16 |       1 |        6 | Classical  | NULL     |
|  7 | Blues     |       7 |      22 |        7 | Popular    | Blues    |
|  8 | Pure      |      38 |       5 |       11 | Popular    | NULL     |
|  9 | Mud       |      19 |      11 |       12 | Popular    | Country  |
| 10 | Essence   |       5 |      23 |       12 | Popular    | New Age  |
| 11 | Embrace   |      21 |      12 |       14 | Popular    | New Age  |
| 12 | Satie     |      42 |      17 |       15 | Classical  | NULL     |
| 13 | Lake      |      23 |      47 |       28 | Classical  | Dance    |
| 14 | Favorites |      34 |      15 |       12 | Classical  | General  |
| 15 | Boheme    |      25 |      12 |        5 | Classical  | Opera    |
| 16 | Cantatas  |      26 |      13 |        8 | Classical  | General  |
| 17 | Road      |      27 |      13 |       17 | Popular    | Country  |
| 18 | Paris     |      18 |      25 |       10 | Popular    | Jazz     |
| 19 | Woman     |      29 |       4 |        7 | Popular    | Blues    |
| 20 | Bach      |      21 |      13 |       16 | Classical  | General  |
| 21 | Opera     |      12 |      32 |       12 | Classical  | Opera    |
| 22 | Soul      |      13 |      30 |       14 | Popular    | NULL     |
| 23 | Stages    |      44 |       0 |        8 | Popular    | Blues    |
| 24 | Bach      |      15 |       1 |        8 | Classical  | General  |
+----+-----------+---------+---------+----------+------------+----------+
24 rows in set (0.00 sec)
mysql> SELECT Name, Category, InStock
    -> FROM Sale
    -> WHERE Category IN ("Blues", "Jazz")
    -> ORDER BY Name;
+--------+----------+---------+
| Name   | Category | InStock |
+--------+----------+---------+
| Blues  | Blues    |       7 |
| Jazz   | Jazz     |      13 |
| Paris  | Jazz     |      18 |
| Stages | Blues    |      44 |
| Woman  | Blues    |      29 |
+--------+----------+---------+
5 rows in set (0.00 sec)

*/
Drop table Sale;

CREATE TABLE Sale
(
   ID SMALLINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
   Name VARCHAR(50) NOT NULL,
   InStock SMALLINT UNSIGNED NOT NULL,
   OnOrder SMALLINT UNSIGNED NOT NULL,
   Reserved SMALLINT UNSIGNED NOT NULL,
   Department ENUM("Classical", "Popular") NOT NULL,
   Category VARCHAR(20)
);

INSERT INTO Sale (Name,      InStock, OnOrder, Reserved, Department, Category)
VALUES          ("Bloodshot", 11,      6,       1,        "Popular",  "Rock"),
                ("Opera",     12,      5,       2,        "Classical","Opera"),
                ("Jazz",      13,      4,       3,        "Popular",  "Jazz"),
                ("Music",     4,       3,       4,        "Classical","Dance"),
                ("Violin",    25,      2,       5,        "Classical", NULL),
                ("Toscana",   16,      1,       6,        "Classical", NULL),
                ("Blues",     7,       22,      7,        "Popular",   "Blues"),
                ("Pure",      38,      5,       11,       "Popular",   NULL),
                ("Mud",       19,      11,      12,       "Popular",  "Country"),
                ("Essence",   5,       23,      12,       "Popular", "New Age"),
                ("Embrace",   21,      12,      14,       "Popular", "New Age"),
                ("Satie",     42,      17,      15,       "Classical", NULL),
                ("Lake",      23,      47,      28,       "Classical", "Dance"),
                ("Favorites", 34,      15,      12,       "Classical", "General"),
                ("Boheme",    25,      12,       5,       "Classical", "Opera"),
                ("Cantatas",  26,      13,      8,        "Classical", "General"),
                ("Road",      27,      13,      17,       "Popular", "Country"),
                ("Paris",     18,      25,      10,       "Popular", "Jazz"),
                ("Woman",     29,      4,        7,       "Popular", "Blues"),
                ("Bach",      21,      13,      16,       "Classical", "General"),
                ("Opera",     12,      32,      12,       "Classical", "Opera"),
                ("Soul",      13,      30,      14,       "Popular", NULL),
                ("Stages",    44,      0,       8,        "Popular", "Blues"),
                ("Bach",      15,      1,       8,        "Classical", "General");
  
select * from Sale;
  
SELECT Name, Category, InStock
FROM Sale
WHERE Category IN ("Blues", "Jazz")
ORDER BY Name;
Источник — «http://sqle.ru/index.php?title=SQL/MySQL/Select_Clause/In&oldid=5283»