Untitled
unknown
plain_text
7 months ago
2.5 kB
9
Indexable
Never
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;
Leave a Comment