Untitled

11/03/2024
===================================================
Join:
A join clause is used for combine rows from  two or more tables based on related columns.

1. inner join 
2. left join  ( left outer join )
3. right join (right outer join)
4. full join (full outer join)

----------------------------------------------------------------------------------------------------------------
1. inner join :- inner join return records that have matching values in both table.

syntax:
select columns from table1
inner join table2
on table1.matching_column=table2.matching_column


use testdb;
create table orders(ord_id int primary key,purch_amt float,ord_date date ,customer_id int, salesman_id int);
insert into orders values(70001 ,      150.5  ,     "2012-10-05",   3005    ,     5002),
(70009    ,   270.65   ,   "2012-09-10"  , 3001  ,       5005),
(70002  ,     65.26   ,    "2012-10-05",   3002     ,    5001),
(70004  ,     110.5    ,   "2012-08-17"  ,3009   ,      5003),
(70007  ,     948.5  ,     "2012-09-10",   3005     ,    5002),
(70005  ,     2400.6   ,   "2012-07-27",   3007     ,    5001),
(70008   ,    5760   ,     "2012-09-10" , 3002   ,      5001);

select * from orders;

create table customer(customer_id int primary key, cust_name varchar(22) ,city varchar(22), grade int);


insert into customer values(3002,"Nick Rimando" ,"New York",100 ),
(3007,"Brad Davis" ,"New York ",200),
(3010,"Graham Zusi" ,"California",200 ),
(3008,"Julian Green" ,"London",300);
select * from orders;
select *  from customer;

select * from orders as o
inner join customer as c
on o.customer_id=c.customer_id;

select o.ord_id,o.ord_date,c.customer_id,c.cust_name from orders o
inner join customer c
on o.customer_id=c.customer_id;

select o.ord_id,o.ord_date,c.customer_id,c.cust_name from orders o
inner join customer c
on o.customer_id=c.customer_id
where ord_id>70002
order by cust_name desc;
-----------------------------------------------------------------------------------------------------------------------

2. left join:- return all records from left table and matching records from right table.
syntax:
select columns from table1
left join table2
on table1.matching_column=table2.matching_column;

exa:-
select * from orders as o
left  join customer as c
on o.customer_id=c.customer_id;

select o.ord_id,o.ord_date,c.customer_id,c.cust_name from orders o
left join customer c
on o.customer_id=c.customer_id;

select o.ord_id,o.ord_date,c.customer_id,c.cust_name from customer c
left join orders o
on o.customer_id=c.customer_id;

select o.ord_id,o.ord_date,c.customer_id,c.cust_name from orders o
left join customer c
on o.customer_id=c.customer_id
where ord_id>70002
order by cust_name desc;

-------------------------------------------------------------------------------------------------------------

3.right join :-return all records from right table and matching records from left table.
syntax:
select columns from table1
right join table2
on table1.matching_column=table2.matching_column;
exa:-
select * from orders as o
right  join customer as c
on o.customer_id=c.customer_id;

select o.ord_id,o.ord_date,c.customer_id,c.cust_name from orders o
right join customer c
on o.customer_id=c.customer_id;

select o.ord_id,o.ord_date,c.customer_id,c.cust_name from customer c
right join orders o
on o.customer_id=c.customer_id;

select o.ord_id,o.ord_date,c.customer_id,c.cust_name from orders o
right join customer c
on o.customer_id=c.customer_id
where ord_id>70002
order by cust_name desc;

--------------------------------------------------------------------------------------------------------------------------------

4. Full join:- return all records from both table either match of right table or left table.
Note:
in mysql full join is not supported.

syntax:
select columns from table1
full join table2
on table1.matching_column=table2.matching_column;
exa:-
select * from orders as o
full outer  join customer as c
on o.customer_id=c.customer_id;

========================================================================================================================
toaday assignment:
Q1. From the following tables write a SQL query to find the salesperson(s) and the customer(s) he represents. Return Customer Name, city, Salesman, commission.

Sample table: customer

 customer_id |   cust_name    |    city    | grade | salesman_id 
-------------+----------------+------------+-------+-------------
        3002 | Nick Rimando   | New York   |   100 |        5001
        3007 | Brad Davis     | New York   |   200 |        5001
        3005 | Graham Zusi    | California |   200 |        5002
        3008 | Julian Green   | London     |   300 |        5002
        3004 | Fabian Johnson | Paris      |   300 |        5006
        3009 | Geoff Cameron  | Berlin     |   100 |        5003
        3003 | Jozy Altidor   | Moscow     |   200 |        5007
        3001 | Brad Guzan     | London     |       |        5005
Sample table: salesman

 salesman_id |    name    |   city   | commission 
-------------+------------+----------+------------
        5001 | James Hoog | New York |       0.15
        5002 | Nail Knite | Paris    |       0.13
        5005 | Pit Alex   | London   |       0.11
        5006 | Mc Lyon    | Paris    |       0.14
        5007 | Paul Adam  | Rome     |       0.13
        5003 | Lauson Hen | San Jose |       0.12