SQL Server Learning Note Series 4

first, write it in front.

A lot of days did not record SQL learning notes, to persist, firmly believe that every point of progress is to accumulate strength. See a picture today and share it.

Through this picture, I see each person standing in their own perspective, feeling is not the same, like learning knowledge, always feel that their understanding is the most unique, sometimes appropriate to share things out

To listen to other people's opinions may make us understand more deeply. Transposition thinking, calm treatment, calm and calm, rashness, bug is only a part of life, because there is a bug will let us into

Step, let us go to learn, to pursue the answer to the question, work together, do a happy program ape. The only constant in the world is change, and learning will adapt us to this change. Keep study,

Keep moving! Get into today's SQL Learning topic.

two. SQL Server 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.shipperid 2   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.productname 2   from production.categories a INNER join production.products b 3  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.orderid 2   from sales.customers a  join Sales.orders b3on  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          as N' customer order quantity '3from    sales.customers a  inner join Sales.orders b4on  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          as N'group-by customer order quantity '3from    sales.customers a  INNER JOIN Sales.orders b4on  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 89 records, you can know that two customers have visited the order, but did not place a single, you can the solution, 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          as N'group-by customer order quantity '3from    sales.customers a  left Join Sales.orders b4on  a.custid=56  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          as N' customer order quantity '3from    sales.customers a right  join Sales.orders b4on  a.custid=56  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          as N' customer order quantity '3  from4  on a.custid=56  ORDER 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.

I hope that you Daniel give guidance, inappropriate to accept learning! Thank you!

SQL Server Learning Note Series 4