Untitled

31/01/2024
--------------------------------------------------
join:
join is used for combine data or rows from two or more tables based on a common feild between them.

Types of joins:
1 inner join 
2 left join 
3 right join 
4 full join

inner join:
the inner join is used for return commmom element from both table as the condition is satisfied.

syntax:
select table1.column1,table1.colum2,---,table2.column1,---
from table1
inner join table2
on table1.match_column=table2.match_col

select * from customers;
select * from orders;

/*select * from customers as c
inner join orders as o
on c.customer_id=o.customer_id*/

select  c.customer_id,c.first_name,o.amount
from customers as c
inner join orders as o
on c.customer_id=o.customer_id;
-----------------------------------------------------------------------------------------------------
left join :
return all records from left table and match records from right table

syntax:
select table1.column1,table1.colum2,---,table2.column1,---
from table1
left join table2
on table1.match_column=table2.match_col

exa:
select  c.customer_id,c.first_name,o.order_id,o.amount
from customers as c
left join orders as o
on c.customer_id=o.customer_id;

--------------------------------------------------------------------------------
right join :
return all records from right table and match records from left table

syntax:
select table1.column1,table1.colum2,---,table2.column1,---
from table1
right join table2
on table1.match_column=table2.match_col

ex:

select  c.customer_id,c.first_name,o.order_id,o.amount,o.customer_id
from customers as c
right join orders as o
on c.customer_id=o.customer_id;
------------------------------------------------------------------------------------------------
full join:-
fetch all records from both table when there is match in either left or right table.

syntax:
select table1.column1,table1.colum2,---,table2.column1,---
from table1
right join table2
on table1.match_column=table2.match_col

exa:
SELECT customers.first_name, orders.order_id
FROM customers
FULL  JOIN orders ON customers.customer_id=orders.customer_id


You can obtain information about foreign keys from the Information Schema KEY_COLUMN_USAGE table. An example of a query against this table is shown here:

mysql> SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, CONSTRAINT_NAME
       FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
       WHERE REFERENCED_TABLE_SCHEMA IS NOT NULL;