SQL Server Learning Notes <> tables connection query----cross Connect, INNER join, left JOIN, right connection

(1) Cross join is what we call the Cartesian product. Query the number of records that satisfy both tables, A (3 Records), B (9 Records), A*b (27 records).

For example: An employee table (hr.employees) and a freight company (sales.shippers) table make a cross-connection.

1  SELECT * FROM hr.employees;2  select * from Sales.shippers;

After the cross-connect, 27 records are found.

1  Select A.empid,b.shipperid2 from  hr.employees a cross join Sales.shippers B;

(2) Inner connection (inner join), i.e. a combination of conditions that must be met.

For example, to find out which category each product belongs to under the Product category table, we need to make a inner join with the associated Product Classification table (production.categories) and the product schedule.

1  Select a.categoryid,a.categoryname,b.productid,b.productname2 from  production.categories a INNER JOIN Production.Products B3 on  A.categoryid=b.categoryid;

Results:

We can see the product 1, belong to the product Category 1. And so on ... so that you can find out what kind of products are in Category 1, and which category each product belongs to.

Here we expand: if we want to find out which customers placed orders, to identify the order of customer information and order information, then we need to link Customer table (sales.customers) and order form

(sales.orders).

By looking at the fields of the two tables, we can see that two tables can be connected with the ID of the CustID customer. Find the relevant customer information and order information.

1  Select A.custid,a.contactname,b.custid,b.orderid2 from  sales.customers a  join Sales.orders B3  on A.custid=b.custid

With the inner join (inner join) You can draw some basic information,

But here we find that some customers have placed a lot of orders, join us to find out the number of orders that the customer has placed, and only show a record of that customer, then we need to use the previously learned

Count.....over usage, returns the number of records. If you want to display a record that is not duplicated, then we can filter it with the keyword distinct.

1  SELECT distinct a.custid,a.contactname,2         count (*) over (partition by A.custid) as N ' Customer order quantity ' 3  from Sales.customers a  inner join sales.orders b4 on  a.custid=b.custid

In this way, we can draw the order quantity of each customer. In fact, we do not need over the window function, but also to achieve the same statistical information, that is, according to CustID Group:

1  Select  a.custid,a.contactname,2         count (*) as N ' group-by Customer order quantity ' 3 from  sales.customers a  inner Join sales.orders b4 on  a.custid=b.custid5  GROUP by A.custid, a.contactname order by A.custid;

Results

Here we come up with the same results as above with Count.....over (). So the choice of the way here, depending on the needs, depending on the situation.

But here we pay attention, we check the Customer table (sales.customers), look at the information inside.

1  SELECT * FROM Sales.customers

We can see a total of 91 records, that is, 91 for customers to patronize the relevant orders, according to the above customer order information of 89 records, you can know that there are two customers visited the order, but did not place a single, can understand, do not buy look at the headquarters!

But we did not see the two people watching the information, how can we look at the two to find out, we gave him two gifts, thank them for their support? This will need to use the next link to the left join.

(3) Left......join, left connection , that is to ensure that the left conditions are all there, the right side of the condition is not sufficient, then null.

Continuing the unfinished task of identifying customer information without placing an order, i.e., 0 of the order number, there must be a guarantee that all customer information is present, that is, the left connection

(Left....join).

1  Select  a.custid,b.custid,a.contactname,a.fax,2         count (b.orderid) as N ' group-by Customer order quantity ' 3  from Sales.customers a left  join sales.orders b4 on  a.custid=b.custid 5  Group by A.custid, A.fax,a.contactname, B.custid 6  ORDER by Count (B.custid);

Results:

(4) Right connection (R .... join), in fact, the right connection with left join, the right side of the table as the benchmark, to ensure that the right table satisfies all records, the left table is not sufficient null. If you swap two table positions, it's fine.

Understanding of the left and right connections.

For example, by connecting the above query, all the customer information for the next order is queried.

1  Select  a.custid,b.custid,a.contactname,a.fax,2         count (b.orderid) as N ' Customer order quantity ' 3  from Sales.customers a right  joins sales.orders b4 on  a.custid=b.custid 5  Group by A.custid, A.fax, A.contactname,b.custid 6  ORDER by Count (B.custid);

According to the above information, we know that there are 89 people who have placed orders, and two have not placed orders, but here we can also find out all the customers ' information by right connection.

1  Select  a.custid,b.custid,a.contactname,a.fax,2         count (b.orderid) as N ' Customer order quantity ' 3 from  sales.orders b Right joins sales.customers a 4 on  A.custid=b.custid 5  Group by A.custid, A.fax,a.contactname,b.custid 6  Ord Er by Count (B.custid);

You can see all the customer information, including the customer information without placing the order. In fact, it's just a two-sheet position.

So, for the left and right connections, it is based on the left table,

Right joins the right table as the benchmark.

SQL Server Learning Notes <> tables connection query----cross Connect, INNER join, left JOIN, right connection