Shulou(Shulou.com)05/31 Report--

This article mainly introduces "the use of cross-connection in SQL Server". In the daily operation, I believe that many people have doubts about the use of cross-connection in SQL Server. The editor consulted all kinds of materials and sorted out simple and easy-to-use operation methods. I hope it will be helpful to answer the doubts about the use of cross-connection in SQL Server. Next, please follow the editor to study!

1. The concept of cross join

A cross join is the first stage of a join query, which carries out a Cartesian product of two data tables. That is, each row of the first data table is joined with all the rows of the second table, and the size of the resulting result set is equal to T1*T2.

Select * from T1 cross join T22, syntax format for cross joins

Select * from T1 cross join T2 cross join-select * from T1 cross join T2 where-select * from T1 cross join T2 where-equivalent to inner join select * from T1 inner join T2 on t1.col1=t2.col23, cross-join usage scenarios 3.1 can query all data

-- example

-- employee form CREATE TABLE [dbo]. [EmpInfo] ([empId] [int] IDENTITY (1pr 1) NOT NULL, [empNo] [varchar] (20) NULL, [empName] [nvarchar] (20) NULL, CONSTRAINT [PK_EmpInfo] PRIMARY KEY CLUSTERED ([empId] ASC) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY]-- Award Table CREATE TABLE [dbo]. [SalaryInfo] ([id] [int] IDENTITY (1) NOT NULL, [empId] [int] NULL, [salary] [decimal] (18,2) NULL, [seasons] [varchar] (20) NULL, CONSTRAINT [PK_SalaryInfo] PRIMARY KEY CLUSTERED ([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]-quarterly table CREATE TABLE [dbo]. [Seasons] ([name] [nchar] (10) NULL) ON [PRIMARY] GO SET IDENTITY_INSERT [dbo]. [EmpInfo] ON INSERT [dbo]. [EmpInfo] ([empId], [empNo], [empName]) VALUES (1, Numba001' N 'Wang Qiang') INSERT [dbo]. [EmpInfo] ([empId], [empNo], [empName]) VALUES (2, Numba002, N`Li Ming') INSERT [dbo]. [EmpInfo] ([empId], [empNo], [empName]) VALUES (3, Numba003) INSERT [dbo]. [SalaryInfo] ([id], [empId], [salary], [seasons]) VALUES (1, 1, CAST (3000.00 AS Decimal (18)) ), N' first quarter') INSERT [dbo]. [SalaryInfo] ([id], [empId], [salary], [seasons]) VALUES (2,3, CAST (5000.00 AS Decimal (18,2)), N 'first quarter') INSERT [dbo]. [SalaryInfo] ([id], [empId], [salary], [seasons]) VALUES (3, 1, CAST (3500.00 AS Decimal (18,2)) N' second quarter') INSERT [dbo]. [SalaryInfo] ([id], [empId], [salary], [seasons]) VALUES (4,3, CAST (3000.00 AS Decimal (18,2)), N' second quarter') INSERT [dbo]. [SalaryInfo] ([id], [empId], [salary], [seasons]) VALUES (5, 2, CAST (4500.00 AS Decimal (18,2)) N' second quarter') INSERT [dbo]. [Seasons] ([name]) VALUES (N' first quarter') INSERT [dbo]. [Seasons] ([name]) VALUES (N' second quarter') INSERT [dbo]. [Seasons] ([name]) VALUES (N' third quarter') INSERT [dbo]. [Seasons] ([name]) VALUES (N' fourth quarter')-query everyone's awards for each quarter If the bonus does not exist, it will be 0 SELECT a.empName. B.name seasons, isnull (c. Salt. 0) salary FROM EmpInfo a CROSS JOIN Seasons b LEFT OUTER JOIN SalaryInfo c ON a.empId=c.empId AND b.name=c.seasons3.2 cross join optimizes query performance

In some cases, cross-join can be used to replace subquery, and the performance of optimized query can be improved by reducing multiple table scans caused by subquery.

4. Summary

Although cross joins support the use of where clauses to filter rows, because Cartesian products can take up a lot of resources, you should avoid using CROSS JOIN if you don't really need Cartesian products. It is recommended to use INNER JOIN instead, it will be more efficient. It can be useful if you need to return data join queries for all possibilities.

At this point, the study on "the use of cross-joins in SQL Server" is over. I hope to be able to solve your doubts. The collocation of theory and practice can better help you learn, go and try it! If you want to continue to learn more related knowledge, please continue to follow the website, the editor will continue to work hard to bring you more practical articles!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.