-
Notifications
You must be signed in to change notification settings - Fork 0
/
joins.sql
73 lines (59 loc) · 1.43 KB
/
joins.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
CREATE TABLE Employee(
id INT PRIMARY KEY
name VARCHAR(20),
salary INT);
INSERT INTO employee VALUES (1,"KAMALIKA",10000),(2,"SRIJIT",10000),(3,"YASH",5000),(4,"KANKANA",4000);
SELECT * FROM employee;
ALTER TABLE employee
ADD COLUMN boss VARCHAR(20);
UPDATE employee
SET boss="HARRY"
WHERE id=1;
UPDATE employee
SET boss="JOSH"
WHERE id=2;
UPDATE employee
SET boss="HARRY"
WHERE id=3;
UPDATE employee
SET boss="POPPY"
WHERE id=4;
/*self join*/
SELECT e.name AS Employee_Name, a.boss AS BOSS
FROM employee e
JOIN employee a
ON e.id = a.id;
CREATE TABLE orders(
order_id INT PRIMARY KEY,
product VARCHAR(20),
price INT);
INSERT INTO orders VALUES(1,"TV",2000),(2,"OVEN",3000),(3,"FAN",1000);
/*inner join*/
SELECT employee.name, orders.product FROM employee
INNER JOIN orders
ON employee.id=orders.order_id;
/*left join*/
SELECT employee.name, orders.product FROM employee
LEFT JOIN orders
ON employee.id=orders.order_id;
/*right join*/
SELECT employee.name, orders.product FROM employee
RIGHT JOIN orders
ON employee.id=orders.order_id;
/*full join*/
SELECT employee.name, orders.product FROM employee
LEFT JOIN orders
ON employee.id=orders.order_id
UNION
SELECT employee.name, orders.product FROM employee
RIGHT JOIN orders
ON employee.id=orders.order_id;
/*xplore*/
SELECT DISTINCT I.Item_Name
FROM Items I
WHERE I.Item_Id NOT IN (
SELECT I.Item_Id
FROM Items I
INNER JOIN Orders O
ON I.Item_Id=O.Item_Id
);