JOINS

 CREATE DATABASE sports;

USE sports;

CREATE TABLE cricket(id INT PRIMARY KEY,name VARCHAR(50), aka VARCHAR(77));

INSERT INTO cricket

VALUES (1,'DRAVID','WALL'),(3,'RAINA','MR.IPL'),

(7,'DHONI','BEST'),

(10,'SACHIN','GOD'),

(18,'KOHLLI','GOAT'),

(17,'ABD','ALIEN'),

(45,'SHANA','HITMAN');

CREATE TABLE football(id INT PRIMARY KEY,name VARCHAR(50), aka VARCHAR(79));

INSERT INTO football

VALUES(4,'RAMOS','WALL'),

(7,'CR7','GOAT'),

(10,'MESSI','ALIEN'),

(11,'NEY','PRINCE'),

(9,'BENZ','HITMAN'),

(100,'MARADONA','GOD'),

(101,'PELE','GOD');

SHOW TABLES;

SELECT * FROM cricket

INNER JOIN football 

ON cricket.aka=football.aka;

SELECT * FROM cricket

LEFT JOIN football

ON cricket.aka =football.aka;

SELECT name FROM cricket

LEFT JOIN football

ON cricket.aka=football.aka;

SELECT * FROM cricket

RIGHT JOIN football

ON cricket.id=football.id;

SELECT cricket.id FROM cricket

RIGHT JOIN football

ON cricket.aka=football.aka;

SELECT * FROM cricket as a

LEFT JOIN football as f

ON a.aka=f.aka

UNION

SELECT*FROM cricket as a

RIGHT JOIN football as f

ON a.aka=f.aka;

SELECT * FROM cricket as c

LEFT JOIN football as f

ON c.aka=f.aka

WHERE f.aka IS NULL;

SELECT * FROM cricket as c

RIGHT JOIN football as f

ON c.aka=f.aka

WHERE c.aka IS NULL;

CREATE TABLE employee (id INT PRIMARY KEY, name VARCHAR(70),manager_id INT);

INSERT INTO employee

VALUES

(1,'joe',100),(2,'jack',3),(3,'rose',2),(4,'rock',12),(12,'lana',null);

SELECT a.name as manager_name,b.name

FROM employee as a

JOIN employee as b

ON a.id=b.manager_id;

SELECT name FROM cricket

UNION

select name from football;