Overview of Different Types of SQL Joins

Examine the various types of SQL joins including INNER JOIN, LEFT JOIN, RIGHT JOIN, and FULL JOIN. Understand the differences between these join types and how they retrieve data from multiple tables based on specified conditions. Explore practical examples of joining tables and creating relationships between them.

• SQL Joins
• INNER JOIN
• LEFT JOIN
• RIGHT JOIN
• FULL JOIN
• Database Management

zhon_yk Follow

Uploaded on Sep 15, 2024 | 0 Views

Download Presentation

Please find below an Image/Link to download the presentation.

The content on the website is provided AS IS for your information and personal use only. It may not be sold, licensed, or shared on other websites without obtaining consent from the author. Download presentation by click this link. If you encounter any issues during the download, it is possible that the publisher has removed the file from their server.

Presentation Transcript

1. SQL Command 02

2. Different Types of SQL JOINs Here are the different types of the JOINs in SQL: (INNER) JOIN: Returns records that have matching values in both tables LEFT (OUTER) JOIN: Returns all records from the left table, and the matched records from the right table RIGHT (OUTER) JOIN: Returns all records from the right table, and the matched records from the left table FULL (OUTER) JOIN: Returns all records when there is a match in either left or right table

3. Different Types of SQL JOINs

4. (INNER) JOIN SELECT column_name(s) FROM table1 INNER JOIN table2 ON table1.column_name = table2.column_name;

5. Join Table Table 2 Table Table Join Table Filed Join ( Field ) Join Table ?????? select * from EX_SaleA,EX_Sale_DetailB where A.Receipt_No=B.Receipt_No

6. LAB JOIN TABLE CREATE TABLE [dbo].[Sales_car]( [Sale_ID] [nvarchar](50) NOT NULL, [Product_ID] [nvarchar](50) NULL, [Color_ID] [nvarchar](50) NULL, CONSTRAINT [PK_Sales_car] PRIMARY KEY CLUSTERED ( [Sale_ID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO ======================================================== delete from sales_car insert into sales_car(Sale_ID,Product_Id,Color_Id) values('S001','PD001','CL001') insert into sales_car values('S002','PD002','CL002')

7. Color delete from color insert into color(Color_id,Color_desc) values('CL001','Red') insert into color values('CL002','Green') insert into color values('CL003','Blue')

8. Join Table Where Sale_ID S001 S002 Product_ID PD001 PD002 Color_ID CL001 CL002 select * from sales_car Color_ID CL001 CL002 CL003 Color_Desc Red Green Blue select * from color Sale_ID S001 S001 S001 S002 S002 S002 Product_ID PD001 PD001 PD001 PD002 PD002 PD002 Color_ID CL001 CL001 CL001 CL002 CL002 CL002 Color_ID CL001 CL002 CL003 CL001 CL002 CL003 Color_Desc Red Green Blue Red Green Blue select * from sales_car,color =2*3=6 ***

9. Join Table Where 2 select * from sales_car Sale_ID S001 S002 Product_ID PD001 PD002 Color_ID CL001 CL002 Color_ID CL001 CL002 CL003 Color_Desc Red Green Blue select * from color Sale_ID S001 S002 Product_ID PD001 PD002 Color_ID CL001 CL002 Color_ID CL001 CL002 Color_Desc Red Green select * from Sales_car,Color where Sales_car.color_id=Color.color_id Color_id

10. Join Table Where, And 2 select * from sales_car Sale_ID S001 S002 Product_ID PD001 PD002 Color_ID CL001 CL002 Color_ID CL001 CL002 CL003 Color_Desc Red Green Blue select * from color select * from Sales_car,Color where Sales_car.color_id=Color.color_id and Sales_car.sale_ID='S001' Sale_ID S001 Product_ID PD001 Color_ID CL001 Color_ID CL001 Color_Desc Red Color_id

11. Join Table Where, And (Alias Table name) 2 select * from sales_car Sale_ID S001 S002 Product_ID PD001 PD002 Color_ID CL001 CL002 Color_ID CL001 CL002 CL003 Color_Desc Red Green Blue select * from color select * from Sales_car A,Color B where A.color_id=B.color_id and A.sale_ID='S001' Sale_ID S001 Product_ID PD001 Color_ID CL001 Color_ID CL001 Color_Desc Red Color_id

12. JOIN 2 select * from Sales_car,Color where Sales_car.color_id=Color.color_id Data Warehouse SELECT * FROM Sales_car INNER JOIN Color ON Sales_car.color_id=Color.color_id Sale_ID Product_ID Color_ID Color_ID Color_Desc S001 PD001 CL001 CL001 Red S002 PD002 CL002 CL002 Green

13. GROUP BY SELECT column_name(s), [Count()],[ Sum()] FROM table_name WHERE condition GROUP BY column_name(s) ORDER BY column_name(s);

14. GROUP BY select Product_type_id,count(Product_id)as Count from T_Product group by Product_type_id Product_Type_Id PT001 PT002 PT004 PT006 PT008 PT009 Count 4 1 1 6 5 3 select Receipt_No,SUM(Total_Amt) as Sum from T_sales_detail group by Receipt_No Receipt_No F16012019G300001 F16012019G300002 F17012019G300001 F17012019G300002 Sum 222 280 92 170

15. INSERT INTO Select INSERT INTO table2 SELECT * FROM table1 WHERE condition; INSERT INTO table2 (column1, column2, column3, ...) SELECT column1, column2, column3, ... FROM table1 WHERE condition;

16. Insert intoSelect INSERT INTO T_Product SELECT * from Product INSERT INTO into Product(Product_Id, Product_NameThai) SELECT Product_Id, Product_NameThai from T_Product

17. Update Select UPDATE Sale_detail SET Sale_detail.unit_price =(SELECT price from Product where Sale_detail.productcode=Product.Productid)

18. Update Select (LAB) insert into TT_Sales_detail select * from T_Sales_detail UPDATE TT_Sales_detail SET TT_Sales_detail.unit_price =(SELECT Unit_price from T_Product WHERE TT_Sales_detail.Product_Id=T_Product.Product_id)