---
title: SQL
category:
tag:
-
- SQL
---
> [ - SQL ](https://www.nowcoder.com/exam/oj?page=1&tab=SQL%E7%AF%87&topicId=298)
##
`select`
### Customers ID
`Customers`
| cust_id |
| ------- |
| A |
| B |
| C |
SQL `Customers` `cust_id`
```sql
select cust_id
from Customers;
```
###
`OrderItems` `prod_id` id
| prod_id |
| ------- |
| a1 |
| a2 |
| a3 |
| a4 |
| a5 |
| a6 |
| a7 |
SQL `prod_id`
```sql
select distinct prod_id
from OrderItems;
```
`distinct`
###
`Customers` `cust_id` id`cust_name`
| cust_id | cust_name |
| ------- | --------- |
| a1 | andy |
| a2 | ben |
| a3 | tony |
| a4 | tom |
| a5 | an |
| a6 | lee |
| a7 | hex |
SQL
```sql
select cust_id, cust_name
from Customers;
```
##
`order by` `desc`
###
`Customers``cust_id` id`cust_name`
| cust_id | cust_name |
| ------- | --------- |
| a1 | andy |
| a2 | ben |
| a3 | tony |
| a4 | tom |
| a5 | an |
| a6 | lee |
| a7 | hex |
`Customers` `cust_name` Z A
```sql
select cust_name from Customers order by cust_name desc
```
### ID
`Orders`
| cust_id | order_num | order_date |
| ------- | --------- | ------------------- |
| andy | aaaa | 2021-01-01 00:00:00 |
| andy | bbbb | 2021-01-01 12:00:00 |
| bob | cccc | 2021-01-10 12:00:00 |
| dick | dddd | 2021-01-11 00:00:00 |
SQL `Orders` ID`cust_id``order_num` ID
```sql
#
# order_date order_num
select cust_id, order_num
from Orders
order by cust_id, order_date desc;
```
`order by`
###
`OrderItems`
| quantity | item_price |
| -------- | ---------- |
| 1 | 100 |
| 10 | 1003 |
| 2 | 500 |
SQL `OrderItems` `quantity``item_price`
```sql
select quantity, item_price
from OrderItems
order by quantity desc, item_price desc;
```
### SQL
`Vendors`
| vend_name |
| --------- |
| |
| |
| |
SQL `vend_name`
```sql
SELECT vend_name,
FROM Vendors
ORDER vend_name DESC;
```
```sql
select vend_name
from Vendors
order by vend_name desc;
```
-
- order by by
##
`where`
`where`
| | |
| :------ | :--------------------------------------------------------- |
| = | |
| | **** SQL != |
| > | |
| < | |
| >= | |
| = 9;
```
###
`Products`
| prod_id | prod_name | prod_price |
| ------- | --------- | ---------- |
| a0011 | egg | 3 |
| a0019 | sockets | 4 |
| b0019 | coffee | 15 |
SQL `Products` 3 6 `prod_name``prod_price`
```sql
select prod_name, prod_price
from Products
where prod_price between 3 and 6
order by prod_price;
#
select prod_name, prod_price
from Products
where prod_price >= 3 and prod_price = 100;
```
##
`and` `or` `and` 2 `or` 2
###
`Vendors` `vend_name``vend_country``vend_state`
| vend_name | vend_country | vend_state |
| --------- | ------------ | ---------- |
| apple | USA | CA |
| vivo | CNA | shenzhen |
| huawei | CNA | xian |
SQL `Vendors` `vend_name`[USA][CA] CA
```sql
select vend_name
from Vendors
where vend_country = 'USA' and vend_state = 'CA';
```
###
`OrderItems`
| prod_id | order_num | quantity |
| ------- | --------- | -------- |
| BR01 | a1 | 105 |
| BR02 | a2 | 1100 |
| BR02 | a2 | 200 |
| BR03 | a4 | 1121 |
| BR017 | a5 | 10 |
| BR02 | a2 | 19 |
| BR017 | a7 | 5 |
SQL 100 `BR01``BR02` `BR03` `OrderItems` `order_num` ID`prod_id``quantity` ID
```sql
select order_num, prod_id, quantity
from OrderItems
where quantity >= 100
and prod_id in('BR01', 'BR02', 'BR03');
```
### 3 6
`Products`
| prod_id | prod_name | prod_price |
| ------- | --------- | ---------- |
| a0011 | egg | 3 |
| a0019 | sockets | 4 |
| b0019 | coffee | 15 |
SQL 3 6 `prod_name``prod_price` AND
```sql
select prod_name, prod_price
from Products
where prod_price between 3 and 6
order by prod_price;
```
### SQL
`Vendors` `vend_name` `vend_country` `vend_state`
| vend_name | vend_country | vend_state |
| --------- | ------------ | ---------- |
| apple | USA | CA |
| vivo | CNA | shenzhen |
| huawei | CNA | xian |
sql
```sql
SELECT vend_name
FROM Vendors
ORDER BY vend_name
WHERE vend_country = 'USA' AND vend_state = 'CA';
```
```sql
select vend_name
from Vendors
where vend_country = 'USA' and vend_state = 'CA'
order by vend_name;
```
`order by` where
##
SQL `LIKE`
SQL
| | |
| :------------------------------- | :------------------------- |
| `%` | |
| `_` | |
| `[charlist]` | |
| `[^charlist]` `[!charlist]` | |
###
`Products`
| prod_name | prod_desc |
| --------- | -------------- |
| a0011 | usb |
| a0019 | iphone13 |
| b0019 | gucci t-shirts |
| c0019 | gucci toy |
| d0019 | lego toy |
SQL `Products` `prod_name``prod_desc` `toy`
```sql
select prod_name, prod_desc
from Products
where prod_desc like '%toy%';
```
###
`Products`
| prod_name | prod_desc |
| --------- | -------------- |
| a0011 | usb |
| a0019 | iphone13 |
| b0019 | gucci t-shirts |
| c0019 | gucci toy |
| d0019 | lego toy |
SQL `Products` `prod_name``prod_desc` `toy`
```sql
select prod_name, prod_desc
from Products
where prod_desc not like '%toy%'
order by prod_name;
```
###
`Products`
| prod_name | prod_desc |
| --------- | ---------------- |
| a0011 | usb |
| a0019 | iphone13 |
| b0019 | gucci t-shirts |
| c0019 | gucci toy |
| d0019 | lego carrots toy |
SQL `Products` `prod_name``prod_desc` `toy` `carrots` `AND` `LIKE`
```sql
select prod_name, prod_desc
from Products
where prod_desc like '%toy%'
and prod_desc like "%carrots%";
```
###
`Products`
| prod_name | prod_desc |
| --------- | ---------------- |
| a0011 | usb |
| a0019 | iphone13 |
| b0019 | gucci t-shirts |
| c0019 | gucci toy |
| d0019 | lego toy carrots |
SQL Products prod_nameprod_desc**** toy carrots `%` `LIKE`
```sql
select prod_name, prod_desc
from Products
where prod_desc like '%toy%carrots%';
```
##
###
`Vendors` `vend_id` id`vend_name` `vend_address` `vend_city`
| vend_id | vend_name | vend_address | vend_city |
| ------- | ------------- | ------------ | --------- |
| a001 | tencent cloud | address1 | shenzhen |
| a002 | huawei cloud | address2 | dongguan |
| a003 | aliyun cloud | address3 | hangzhou |
| a003 | netease cloud | address4 | guangzhou |
SQL `Vendors` `vend_id``vend_name``vend_address` `vend_city` `vend_name` `vname` `vend_city` `vcity` `vend_address` `vaddress`
```sql
select vend_id, vend_name as vname, vend_address as vaddress, vend_city as vcity
from Vendors
order by vname;
# as
select vend_id, vend_name vname, vend_address vaddress, vend_city vcity
from Vendors
order by vname;
```
###
10%`Products` `prod_id` id`prod_price`
SQL `Products` `prod_id``prod_price` `sale_price``sale_price` 0.9 90% 10%
```sql
select prod_id, prod_price, prod_price * 0.9 as sale_price
from Products;
```
`sale_price`
##
###
`Customers`
| cust_id | cust_name | cust_contact | cust_city |
| ------- | --------- | ------------ | --------- |
| a1 | Andy Li | Andy Li | Oak Park |
| a2 | Ben Liu | Ben Liu | Oak Park |
| a3 | Tony Dai | Tony Dai | Oak Park |
| a4 | Tom Chen | Tom Chen | Oak Park |
| a5 | An Li | An Li | Oak Park |
| a6 | Lee Chen | Lee Chen | Oak Park |
| a7 | Hex Liu | Hex Liu | Oak Park |
SQL ID`cust_id``cust_name``user_login``cust_contact``cust_city`
```sql
select
cust_id,
cust_name,
upper(concat(substring(cust_contact, 1, 2), substring(cust_city, 1, 3))) as user_login
from
Customers;
```
- `substring()``substring(str ,n ,m)` str n m
- `concat()`select concat(A,B) A B
- `upper()`
### 2020 1
`Orders`
| order_num | order_date |
| --------- | ------------------- |
| a0001 | 2020-01-01 00:00:00 |
| a0002 | 2020-01-02 00:00:00 |
| a0003 | 2020-01-01 12:00:00 |
| a0004 | 2020-02-01 00:00:00 |
| a0005 | 2020-03-01 00:00:00 |
SQL 2020 1 `order_num``order_date`
```sql
select order_num, order_date
from Orders
where month(order_date) = '01' and year(order_date) = '2020'
order by order_date;
```
```sql
select order_num, order_date
from Orders
where order_date like '2020-01%'
order by order_date;
```
- `YYYY-MM-DD`
- `HH:MM:SS`
| | |
| --------------- | ------------------------------ |
| `adddate()` | |
| `addtime()` | |
| `curdate()` | |
| `curtime()` | |
| `date()` | |
| `datediff()` | |
| `date_format()` | |
| `day()` | |
| `dayofweek()` | |
| `hour()` | |
| `minute()` | |
| `month()` | |
| `now()` | |
| `second()` | |
| `time()` | |
| `year()` | |
##
| | |
| --------- | ---------------- |
| `avg()` | |
| `count()` | |
| `max()` | |
| `min()` | |
| `sum()` | |
###
`OrderItems` `quantity`
| quantity |
| -------- |
| 10 |
| 100 |
| 1000 |
| 10001 |
| 2 |
| 15 |
SQL
```sql
select sum(quantity) as items_ordered
from OrderItems;
```
### BR01
`OrderItems` `quantity` `prod_id`
| quantity | prod_id |
| -------- | ------- |
| 10 | AR01 |
| 100 | AR10 |
| 1000 | BR01 |
| 10001 | BR010 |
`prod_id`"BR01"
```sql
select sum(quantity) as items_ordered
from OrderItems
where prod_id = 'BR01';
```
### Products 10
`Products` `prod_price`
| prod_price |
| ---------- |
| 9.49 |
| 600 |
| 1000 |
SQL `Products` 10 `prod_price` `max_price`
```sql
select max(prod_price) as max_price
from Products
where prod_price = 100
order by order_num;
#
select order_num
from (select order_num, sum(quantity) as sum_num
from OrderItems group by order_num having sum_num >= 100
) a
order by order_num;
```
- `where`
- `having` `group by`
###
`OrderItems` `order_num` `item_price` `quantity`
| order_num | item_price | quantity |
| --------- | ---------- | -------- |
| a1 | 10 | 105 |
| a2 | 1 | 1100 |
| a2 | 1 | 200 |
| a4 | 2 | 1121 |
| a5 | 5 | 10 |
| a2 | 1 | 19 |
| a7 | 7 | 5 |
SQL 1000
= item_price quantity
```sql
select order_num, sum(item_price * quantity) as total_price
from OrderItems
group by order_num
having total_price >= 1000
order by order_num;
```
### SQL
`OrderItems` `order_num`
| order_num |
| --------- |
| a002 |
| a002 |
| a002 |
| a004 |
| a007 |
```sql
SELECT order_num, COUNT(*) AS items
FROM OrderItems
GROUP BY items
HAVING COUNT(*) >= 3
ORDER BY items, order_num;
```
```sql
SELECT order_num, COUNT(*) AS items
FROM OrderItems
GROUP BY order_num
HAVING items >= 3
ORDER BY items, order_num;
```
##
SQL `select` SQL
`select``insert``update` `delete` `=````in``between``exists`
`where` `from`
- `where` WHERE
- `from` `from`
> MySQL 4.1
`where`
```sql
select column_name [, column_name ]
from table1 [, table2 ]
where column_name operator
(select column_name [, column_name ]
from table1 [, table2 ]
[where])
```
- `( )`
- `operator` where
`from`
```sql
select column_name [, column_name ]
from (select column_name [, column_name ]
from table1 [, table2 ]
[where]) as temp_table_name
where condition
```
`from` AS
### 10
`OrderItems` `order_num``item_price``Orders` `idcust_id` `order_num`
`OrderItems` :
| order_num | item_price |
| --------- | ---------- |
| a1 | 10 |
| a2 | 1 |
| a2 | 1 |
| a4 | 2 |
| a5 | 5 |
| a2 | 1 |
| a7 | 7 |
`Orders`
| order_num | cust_id |
| --------- | ------- |
| a1 | cust10 |
| a2 | cust1 |
| a2 | cust1 |
| a4 | cust2 |
| a5 | cust5 |
| a2 | cust1 |
| a7 | cust7 |
10
```sql
select cust_id
from Orders
where order_num in (
select order_num
from OrderItems
group by order_num
having sum(item_price) >= 10
);
```
### prod_id BR01
`OrderItems` `prod_id` id`Orders` `cust_id` id `order_date`
`OrderItems`
| prod_id | order_num |
| ------- | --------- |
| BR01 | a0001 |
| BR01 | a0002 |
| BR02 | a0003 |
| BR02 | a0013 |
`Orders`
| order_num | cust_id | order_date |
| --------- | ------- | ------------------- |
| a0001 | cust10 | 2022-01-01 00:00:00 |
| a0002 | cust1 | 2022-01-01 00:01:00 |
| a0003 | cust1 | 2022-01-02 00:00:00 |
| a0013 | cust2 | 2022-01-01 00:20:00 |
SQL `OrderItems` `prod_id` "BR01" `Orders` ID`cust_id``order_date`
```sql
# 1
select cust_id, order_date
from Orders
where order_num in (
select order_num
from OrderItems
where prod_id = 'BR01'
)
order by order_date;
# 2:
select
b.cust_id, b.order_date
from
OrderItems a, Orders b
where
a.order_num = b.order_num
and a.prod_id = 'BR01'
order by
order_date;
```
### prod_id BR01
BR01 `OrderItems` `prod_id` id`Orders` `cust_id` id `order_date``Customers` `cust_email` `cust_id` id
`OrderItems`
| prod_id | order_num |
| ------- | --------- |
| BR01 | a0001 |
| BR01 | a0002 |
| BR02 | a0003 |
| BR02 | a0013 |
`Orders`
| order_num | cust_id | order_date |
| --------- | ------- | ------------------- |
| a0001 | cust10 | 2022-01-01 00:00:00 |
| a0002 | cust1 | 2022-01-01 00:01:00 |
| a0003 | cust1 | 2022-01-02 00:00:00 |
| a0013 | cust2 | 2022-01-01 00:20:00 |
`Customers` `cust_id` id`cust_email` email
| cust_id | cust_email |
| ------- | --------------- |
| cust10 | cust10@cust.com |
| cust1 | cust1@cust.com |
| cust2 | cust2@cust.com |
`prod_id` `BR01` `Customers` `cust_email`
`SELECT` `OrderItems` `order_num` `Customers` `cust_id`
```sql
# 1
select cust_email
from Customers
where cust_id in (
select cust_id
from Orders
where order_num in (
select order_num
from OrderItems
where prod_id = 'BR01'
)
);
# 2: inner join
select c.cust_email
from OrderItems a, Orders b, Customers c
where a.order_num = b.order_num
and b.cust_id = c.cust_id
and a.prod_id = 'BR01';
# 3left join
select
c.cust_email
from
Orders a
left join OrderItems b on a.order_num = b.order_num
left join Customers c on a.cust_id = c.cust_id
where
b.prod_id = 'BR01';
```
###
ID
`OrderItems` `OrderItems` `order_num` `item_price``quantity`
| order_num | item_price | quantity |
| --------- | ---------- | -------- |
| a0001 | 10 | 105 |
| a0002 | 1 | 1100 |
| a0002 | 1 | 200 |
| a0013 | 2 | 1121 |
| a0003 | 5 | 10 |
| a0003 | 1 | 19 |
| a0003 | 7 | 5 |
`Orders` `order_num` id`cust_id`
| order_num | cust_id |
| --------- | ------- |
| a0001 | cust10 |
| a0002 | cust1 |
| a0003 | cust1 |
| a0013 | cust2 |
SQL ID`Orders` `cust_id` `total_ordered`
```sql
# 1
SELECT
o.cust_id cust_id,
tb.total_ordered total_ordered
FROM (
SELECT order_num, SUM(item_price * quantity) total_ordered
FROM OrderItems
GROUP BY order_num
) as tb,
Orders o
WHERE
tb.order_num = o.order_num
ORDER BY
total_ordered DESC;
# 2
select b.cust_id, sum(a.quantity * a.item_price) as total_ordered
from OrderItems a, Orders b
where a.order_num = b.order_num
group by cust_id
order by total_ordered desc;
```
### Products
`Products` `prod_name` id`prod_id`
| prod_id | prod_name |
| ------- | --------- |
| a0001 | egg |
| a0002 | sockets |
| a0013 | coffee |
| a0003 | cola |
`OrderItems` `prod_id``quantity`
| prod_id | quantity |
| ------- | -------- |
| a0001 | 105 |
| a0002 | 1100 |
| a0002 | 200 |
| a0013 | 1121 |
| a0003 | 10 |
| a0003 | 19 |
| a0003 | 5 |
SQL `Products` `prod_name` `quant_sold` `OrderItems` `SUM(quantity)`
```sql
# 1
select p.prod_name, tb.quant_sold
from (
select prod_id, sum(quantity) as quant_sold
from OrderItems
group by prod_id
) as tb,
Products p
where tb.prod_id = p.prod_id;
# 2
select p.prod_name, sum(o.quantity) as quant_sold
from Products p, OrderItems o
where p.prod_id = o.prod_id
group by p.prod_name; p.prod_id
```
##
JOIN SQL JOIN
****
`JOIN`
```sql
select table1.column1, table2.column2...
from table1
join table2
on table1.common_column1 = table2.common_column2;
```
`table1.common_column1 = table2.common_column2` => **select **
### prod_id BR01
`OrderItems` `prod_id` id`Orders` `cust_id` id `order_date`
`OrderItems`
| prod_id | order_num |
| ------- | --------- |
| BR01 | a0001 |
| BR01 | a0002 |
| BR02 | a0003 |
| BR02 | a0013 |
`Orders`
| order_num | cust_id | order_date |
| --------- | ------- | ------------------- |
| a0001 | cust10 | 2022-01-01 00:00:00 |
| a0002 | cust1 | 2022-01-01 00:01:00 |
| a0003 | cust1 | 2022-01-02 00:00:00 |
| a0013 | cust2 | 2022-01-01 00:20:00 |
SQL `OrderItems` `prod_id` "BR01" `Orders` ID`cust_id``order_date`
```sql
# 1
select cust_id, order_date
from Orders
where order_num in (
select order_num
from OrderItems
where prod_id = 'BR01'
)
order by order_date;
# 2 inner join
select cust_id, order_date
from Orders o inner join (
select order_num
from OrderItems
where prod_id = 'BR01'
) tb on o.order_num = tb.order_num
order by order_date;
# 3 2
select cust_id, order_date
from Orders
inner join OrderItems using(order_num)
where OrderItems.prod_id = 'BR01'
order by order_date;
```
### prod_id BR01
`OrderItems` `prod_id` id`Orders` `cust_id` id `order_date``Customers` `cust_email` cust_id id
`OrderItems`
| prod_id | order_num |
| ------- | --------- |
| BR01 | a0001 |
| BR01 | a0002 |
| BR02 | a0003 |
| BR02 | a0013 |
`Orders`
| order_num | cust_id | order_date |
| --------- | ------- | ------------------- |
| a0001 | cust10 | 2022-01-01 00:00:00 |
| a0002 | cust1 | 2022-01-01 00:01:00 |
| a0003 | cust1 | 2022-01-02 00:00:00 |
| a0013 | cust2 | 2022-01-01 00:20:00 |
`Customers` `cust_id` id`cust_email` email
| cust_id | cust_email |
| ------- | --------------- |
| cust10 | cust10@cust.com |
| cust1 | cust1@cust.com |
| cust2 | cust2@cust.com |
`prod_id` BR01 `Customers` `cust_email`
`SELECT` `OrderItems` `order_num` `Customers` `cust_id` INNER JOIN
```sql
select cust_email
from Customers
inner join Orders using(cust_id)
inner join OrderItems using(order_num)
where OrderItems.prod_id = 'BR01';
```
###
`OrderItems` `OrderItems` `order_num` `item_price` `quantity`
| order_num | item_price | quantity |
| --------- | ---------- | -------- |
| a1 | 10 | 105 |
| a2 | 1 | 1100 |
| a2 | 1 | 200 |
| a4 | 2 | 1121 |
| a5 | 5 | 10 |
| a2 | 1 | 19 |
| a7 | 7 | 5 |
`Orders` `order_num` `cust_id` id
| order_num | cust_id |
| --------- | -------- |
| a1 | cust10 |
| a2 | cust1 |
| a3 | cust2 |
| a4 | cust22 |
| a5 | cust221 |
| a7 | cust2217 |
`Customers` `cust_id` id`cust_name`
| cust_id | cust_name |
| -------- | --------- |
| cust10 | andy |
| cust1 | ben |
| cust2 | tony |
| cust22 | tom |
| cust221 | an |
| cust2217 | hex |
SQL 1000 `OrderItems` `order_num`
`item_price` `quantity` INNER JOIN
```sql
select cust_name, sum(item_price * quantity) as total_price
from Customers
inner join Orders using(cust_id)
inner join OrderItems using(order_num)
group by cust_name
having total_price >= 1000
order by total_price;
```
##
###
`Customers` id `cust_id` `cust_name`
| cust_id | cust_name |
| -------- | --------- |
| cust10 | andy |
| cust1 | ben |
| cust2 | tony |
| cust22 | tom |
| cust221 | an |
| cust2217 | hex |
`Orders` `order_num` id `cust_id`
| order_num | cust_id |
| --------- | -------- |
| a1 | cust10 |
| a2 | cust1 |
| a3 | cust2 |
| a4 | cust22 |
| a5 | cust221 |
| a7 | cust2217 |
INNER JOIN SQL `Customers` `cust_name``Orders` `order_num` `cust_name`
```sql
select cust_name, order_num
from Customers
inner join Orders
using(cust_id)
order by cust_name;
```
###
`Orders` `order_num` id `cust_id`
| order_num | cust_id |
| --------- | -------- |
| a1 | cust10 |
| a2 | cust1 |
| a3 | cust2 |
| a4 | cust22 |
| a5 | cust221 |
| a7 | cust2217 |
`Customers` id `cust_id` `cust_name`
| cust_id | cust_name |
| -------- | --------- |
| cust10 | andy |
| cust1 | ben |
| cust2 | tony |
| cust22 | tom |
| cust221 | an |
| cust2217 | hex |
| cust40 | ace |
`Customers` `cust_name`Orders `order_num` `cust_name`
```sql
select cust_name, order_num
from Customers
left join Orders
using(cust_id)
order by cust_name;
```
###
`Products` `prod_id` id`prod_name`
| prod_id | prod_name |
| ------- | --------- |
| a0001 | egg |
| a0002 | sockets |
| a0013 | coffee |
| a0003 | cola |
| a0023 | soda |
`OrderItems` `order_num` id `prod_id`
| prod_id | order_num |
| ------- | --------- |
| a0001 | a105 |
| a0002 | a1100 |
| a0002 | a200 |
| a0013 | a1121 |
| a0003 | a10 |
| a0003 | a19 |
| a0003 | a5 |
left join right joinfull join `Products` `OrderItems` `prod_name``order_num`
```sql
select prod_name, order_num
from Products
left join OrderItems
using(prod_id)
order by prod_name;
```
###
`Products` `prod_id` id`prod_name`
| prod_id | prod_name |
| ------- | --------- |
| a0001 | egg |
| a0002 | sockets |
| a0013 | coffee |
| a0003 | cola |
| a0023 | soda |
`OrderItems` `order_num` id `prod_id`
| prod_id | order_num |
| ------- | --------- |
| a0001 | a105 |
| a0002 | a1100 |
| a0002 | a200 |
| a0013 | a1121 |
| a0003 | a10 |
| a0003 | a19 |
| a0003 | a5 |
OUTER JOIN `Products` `OrderItems` `prod_name`
```sql
select prod_name, count(order_num) as orders
from Products
left join OrderItems
using(prod_id)
group by prod_name
order by prod_name;
```
###
`Vendors` `vend_id` id
| vend_id |
| ------- |
| a0002 |
| a0013 |
| a0003 |
| a0010 |
`Products` `vend_id` id prod_id id
| vend_id | prod_id |
| ------- | -------------------- |
| a0001 | egg |
| a0002 | prod_id_iphone |
| a00113 | prod_id_tea |
| a0003 | prod_id_vivo phone |
| a0010 | prod_id_huawei phone |
`Vendors` `vend_id` OUTER JOIN COUNT() `Products` vend_id
`vend_id`
```sql
select vend_id, count(prod_id) as prod_id
from Vendors
left join Products
using(vend_id)
group by vend_id
order by vend_id;
```
##
`UNION` `UNION`
`UNION`
-
-
-
`UNION` `UNION ALL`
```sql
SELECT column_name(s) FROM table1
UNION ALL
SELECT column_name(s) FROM table2;
```
`UNION` `UNION` `SELECT`
`JOIN` vs `UNION`
- `JOIN` `UNION`
- `UNION` `JOIN`
### SELECT
`OrderItems` `prod_id` id`quantity`
| prod_id | quantity |
| ------- | -------- |
| a0001 | 105 |
| a0002 | 100 |
| a0002 | 200 |
| a0013 | 1121 |
| a0003 | 10 |
| a0003 | 19 |
| a0003 | 5 |
| BNBG | 10002 |
`SELECT` `OrderItems` id`prod_id` `quantity` `SELECT` 100 `SELECT` id BNBG id
```sql
select prod_id, quantity
from OrderItems
where quantity = 100
union
select prod_id, quantity
from OrderItems
where prod_id like 'BNBG%';
```
### SELECT
`OrderItems` `prod_id` id`quantity`
| prod_id | quantity |
| ------- | -------- |
| a0001 | 105 |
| a0002 | 100 |
| a0002 | 200 |
| a0013 | 1121 |
| a0003 | 10 |
| a0003 | 19 |
| a0003 | 5 |
| BNBG | 10002 |
`SELECT` `OrderItems` id`prod_id` `quantity` `SELECT` 100 `SELECT` id BNBG id ** SELECT **
select `or` `union`
```sql
select prod_id, quantity
from OrderItems
where quantity = 100 or prod_id like 'BNBG%';
```
### Products Customers
`Products` `prod_name`
| prod_name |
| --------- |
| flower |
| rice |
| ring |
| umbrella |
Customers cust_name
| cust_name |
| --------- |
| andy |
| ben |
| tony |
| tom |
| an |
| lee |
| hex |
SQL `Products` `prod_name` `Customers` `cust_name`
```sql
# UNION UNION SELECT
select prod_name
from Products
union
select cust_name
from Customers
order by prod_name;
```
### SQL
`Customers` `cust_name` `cust_contact` `cust_state` `cust_email` `email`
| cust_name | cust_contact | cust_state | cust_email |
| --------- | ------------ | ---------- | --------------- |
| cust10 | 8695192 | MI | cust10@cust.com |
| cust1 | 8695193 | MI | cust1@cust.com |
| cust2 | 8695194 | IL | cust2@cust.com |
SQL
```sql
SELECT cust_name, cust_contact, cust_email
FROM Customers
WHERE cust_state = 'MI'
ORDER BY cust_name;
UNION
SELECT cust_name, cust_contact, cust_email
FROM Customers
WHERE cust_state = 'IL'ORDER BY cust_name;
```
```sql
SELECT cust_name, cust_contact, cust_email
FROM Customers
WHERE cust_state = 'MI'
UNION
SELECT cust_name, cust_contact, cust_email
FROM Customers
WHERE cust_state = 'IL'
ORDER BY cust_name;
```
`union` `order by` `select`
`or`
```sql
SELECT cust_name, cust_contact, cust_email
FROM Customers
WHERE cust_state = 'MI' or cust_state = 'IL'
ORDER BY cust_name;
```