Database knowledge Collation < eight >

Join:

8.1 Understanding Simple Single joins:　

　　The result of the connection is basically the Cartesian product of each set. For example: A Cartesian product of two sets {A,b,c} and {A/b} is a paired set of{(a,a), (A, B), (B,a), (B,b), (C,a), (C,b)}.

Select Table 1. Column 1, table 1. Column 2, table 2. Column 3 from table 1 Cross join table 2;

This type of join is called a cross join, or a Cartesian product. This is how the query statement is written in MySQL.

8.1.1 uses two table equality joins:

The most common method for connecting rows of two tables is through an equal join. An equality join is based on the value of one column in each row.　　such as SQL statements:Select table 1. Column 1, table 1. Column 2, table 2. Column 3 from table 1, table 2 where table 1. Column 1 = table 2. column 2; As with cross joins, we can use a clearer keyword form to express the preceding statement. such as:Select Table 1. Column 1, table 1. Column 2, table 2. Column 3 from table 1 join table 2 on table 1. Column 1 = table 2. column 2; the difference here is that we replace the comma with the join instead of the where, but it is important to note that Although where is replaced with on, the WHERE keyword can be used in such statements, not to say where it is not available, which needs to be noted. For example:

Select Studentexam.examid,studentexam.makr,student.name as Studentname from Studentexam join Student on Studentexam.studnetid = Student.studnetid where Studentexam.mark >= 80; (Additional conditional scores are greater than or equal to 80 of student information.) ）

This is the standard form.　 to make the syntax of this example work on all database platforms and get the same results, we have to use: INNER join instead of join.

According to the above statement we can solve something like this, such as: Suppose there is a table customers with a CustomerID column, there is a table creditcards also with CustomerID column, I can write the following statement:

Select Customers.customerid,customers.customersname,creditcards.cardsnumber from Customers join CreditCards on Customers.CustomerID = Creditcards.customerid;

Suppose these two tables are a one-to-many relationship, and a user can have multiple credit cards. The query results will return one row for each credit card in the CreditCards table. Note that some lines have the same customer information, because that's how we request data.

8.1.2 Using multiple table equality joins:

An equality join is not limited to two tables, and I can include more tables in the join. Adding join and on clauses after a join and on clause allows you to join more tables. For example: SELECT * FROM table1 joins table2 on table1.column1 = Table2.column2 joins table3 on table1.column3 = TABLE3.COLUMN4;

For the above statement, we have increased the table for the previous customer and credit card, assuming that there is another table Address table that stores one or more addresses for each customer. I can execute the following statements:

Select Customer.customerid,customer.customername,credicards.cardnumber,address.country from Customer join Credicards on customer.customerid = Credicards.customerid join Address on customer.customerid = Address.customerid;

8.1.3 using non-equality joins:

All equality joins are used to wait for an operator (=) after on, and of course we can use other comparison operators in joins. For example: SELECT * FROM table1 joins table2 on Table1.column1 < Table2.column2;

8.1.4 using aliases in an equality join:

We can see from the above example that each day the name of the record is very long, of course we can also use the (AS) keyword alias to make the look more concise and clear. Like what:

Select SE. Examid,se. Mark,s.name as studentname from Studentexam as SE joins Student as S on SE. StudentID = S.studentid ORDER by Examid;

This saves space and makes the query easier to read.

8.2 Using joins:

When tables are joined together, we combine rows from one table with rows from other tables and extract data from the group. Joins can be divided into the following categories:

1. INNER JOIN: is a typical join operation that uses a comparison operator like = or <>. Includes equality joins, non-equal joins, and natural joins. Inner joins use the parity operator to match rows in two tables based on the values of the columns that are common to each table. For example: Retrieve all rows of the same roomid in the class table.
2. Outer joins: Outer joins can also be divided into left outer joins, right outer joins or whole outer joins. When you specify an outer join in the FROM clause, you can specify a set of the following keywords:

• Left OUTER join: either a ieft join or a outer join. The result set of the left outer join includes all rows of the left table specified in the outer clause, not just the rows that match. If a row in the left table does not have a matching row in the right table, all selection lists in the right table in the associated result set row are null values.
• Right outer join: either a join or a outer join. The right outer join is opposite to the left outward join, and its usage is the same.
• Full outer join: either an entire join or an outer join. A full outer join returns all rows of the left and right tables, and when a row does not have a matching row in another table, the selection column of the other table contains a null value. If there are matching rows between the tables, the entire result set row contains the data values of the base table.

3. Cross join: The Cross join returns all rows from the left table, and each row in the left table is combined with all rows in the right table. Cross joins are also called Cartesian product.

1. Inner joins: The use of comparison operators to match rows in two tables based on the values of the columns that are common to each table.　　For example: First we perform an inner join in two tables. Select Table 1. Column 1, table 2. Column 2 from table 1 inner JOIN table 2 on table 1. Column 1 = table 2. column 2;
2. Outer joins: Outer joins take into account the case where some rows in the source table do not match. There are three forms of outer joins: left, right, outside, all outside. The result set, respectively, contains all the tables from the left, the table on the right, and both sides of the table. The syntax for an outer join is similar to an inner join, but uses a left outer join, a right outer join, and a full outer join. All of these joins require an on word.

• Left OUTER JOIN statement: SELECT * FROM table1 ieft outer join table2 on table1.column1 = Table2.column2;
• Right OUTER JOIN statement: SELECT * FROM table1-outer join table2 on table1.column1 = Table2.column2;
• Full OUTER JOIN statement: SELECT * FROM table1 full outer joins table2 on table1.column1 = Table2.column2;

Summary: The connection of database tables is undoubtedly a very important point of knowledge, in peacetime learning and use of the process could not feel its wonderful place. This knowledge point is also a question that is favored by countless interviewers. For example: Do you know several forms of a join? What are the outer joins divided into? What is Cartesian product? Wait ... So familiarity with mastering it is extremely important. Summing up here, the basic knowledge of the database is finished. After these days of summary and review, nature is the use of the database and a bit more sentiment, the basic knowledge of the database is familiar with. In order to facilitate in the future work can be learned to apply to the actual problem solving, this is the true meaning.

　　

Database knowledge Collation < eight >