SQL join and sqljoin
================================================================================================ ====
1. Theoretical knowledge points
SQL join
SQL join is used to query data from two or more tables based on the relationship between columns. Sometimes we need to obtain results from two or more tables to get complete results. We need to execute join. Tables in the database can be connected by keys. A Primary Key is a column in which the values of each row are unique. In the table, the values of each primary key are unique. In this way, the data in each table is bundled together without repeating all the data in each table.
Connection query is the most important query in relational databases, including internal connections, external connections, and cross connections. You can use the join operator to query multiple tables. Connection is the main feature of the relational database model and a symbol that distinguishes it from other types of database management systems. In the relational database management system, the relationship between data does not have to be determined when a table is created, and all information about an object is often stored in a table. When retrieving data, you can use the join operation to query information about different entities in multiple tables. Connection operations bring great flexibility to users. They can add new data types at any time. Create new tables for different entities, and then query them through connections.
Different SQL JOIN
JOIN types and their differences:
- [INNER] JOIN: inner join. If there is at least one match in the table, the row is returned.
- Left join: returns all rows from the LEFT table even if no match exists in the right table.
- Right join: returns all rows from the RIGHT table even if no match exists in the left table.
- Full join: if one of the tables matches, the row is returned.
Cross joinA cross-join is the product of a flute. It refers to any combination of all tuples in two relationships. Generally, cross-query is meaningless. For example, if you want to get the product of the two link modes of the student table and the Selected Course table, the query statement is SELECT * FROM the student table cross join Course Selection table.
Internal ConnectionIntranet connections are the most common connection types. Intranet connection query is actually a query with any conditions. When using inner join, if the related fields of the two tables meet the join condition, extract data from the two tables and combine them into new records, that is, in the inner join query, only the qualified tuples can appear in the result relationship. For example, to query the status of each selected student, the query statement is SELECT * FROM student table inner join Course Selection table ON student table. Student ID = Course Selection table. Student ID can be divided:
1) equijoin:Use the equal sign (=) operator in the connection condition to compare the column values of the connected columns. All columns in the connected table, including duplicate columns, are listed in the query results.
2) Unequal connections:Use a comparison operator other than the equal operator to compare the values of the joined columns in the join condition. These operators include >,>=, <=, <,!> ,! <And <>.
3) natural connection:Use the equal to (=) operator in the join condition to compare the column values of the connected columns. However, it uses the selection list to indicate the columns included in the query result set and delete duplicate columns in the join table.
Self-connectionIf two tables involved in a connection query are the same table, this query is called a self-join query. The same table appears multiple times in the FROM clause. To differentiate each appearance of the table, you must define an alias for the table. Self-join is a special internal join. It refers to the physical connection of a table to the same table, but it can be logically divided into two tables. For example, if you want to retrieve the information of a classmate whose student ID is 20210, the query statement is the SELECT student table. * FROM student table JOIN student table AS student table 1ON student table. class = student table 1. class WHERE student table 1. student ID = '000000'
External ConnectionThe query results of inner join are all tuples that meet the connection conditions. However, sometimes we want to output the information of tuples that do not meet the connection conditions. For example, we want to know the course selection situation of each student, including the students who have selected the course (the student ID is included in the student table and also exists in the Course Selection table, which meets the connection conditions ), it also includes students without course selection (the student ID of this part is in the student table, but it is not in the Course Selection table and does not meet the connection conditions). In this case, an external connection is required. External join only limits the connection conditions for the data in one table, while the data in the other table does not. Three types of external connections:
1) left Outer JOIN)If all the tuples in the table on the left of the connection pipe are listed in the connection query and the matched tuples can be found in the table on the right, the connection is successful. If the matched tuples are not found in the right table, the corresponding tuples are NULL ). In this case, the query statement uses the keyword left outerjoin. That is to say, the meaning of the LEFT Outer Join is that the data in the table on the right of the join keyword must meet the join conditions, if the data in the left-end table does not meet the connection conditions, the content in the left-end table is output. For example, to query the Course Selection of all students, including students who have selected and have not yet selected the course, the query statement is the SELECT student table. student ID, name, class, course number, score FROM student table left outer join Course Selection table ON student table. student ID = Course Selection table. the information of all tuples in the left-side table in the student ID left outer join query is retained.
2) Right Outer Join (RIGHTOUTERJOIN)The right outer join is similar to the left outer join, but all the tuples in the right end table are listed. The data in the left end table must meet the join conditions, regardless of whether the data in the right end table meets the join conditions, all output table content. For example, in the preceding example, the query statement is the SELECT student table. student ID, name, class, course number, score FROM student table RIGHTOUTERJOIN Course Selection table ON student table. student ID = Course Selection table. the information of all tuples in the right table of the student ID's right outer join query is retained.
3) FULL OUTER JOIN)Full outer join query is characterized by the output of tuples in tables on both left and right sides. If the matching tuples cannot be found, NULL is used instead. For example, in the example of a left outer join, the query statement is the SELECT student table. student ID, name, class, course number, score FROM student table full outer join Course Selection table ON student table. student ID = Course Selection table. the information of the tuples in all tables in the student ID's full outer join query is retained.
==============================================================================
2. Set up an instance Database
"Persons" table:
| Id_P |
LastName |
FirstName |
Address |
City |
| 1 |
Adams |
John |
Oxford Street |
London |
| 2 |
Bush |
George |
Th Avenue |
New York |
| 3 |
Carter |
Thomas |
Changan Street |
Beijing |
The "Id_P" column is the primary key of the Persons table.
"Orders" table:
| Id_O |
OrderNo |
Id_P |
| 1 |
77895 |
3 |
| 2 |
44678 |
3 |
| 3 |
22456 |
1 |
| 4 |
24562 |
1 |
| 5 |
34764 |
65 |
The "Id_O" column is the primary key of the Orders table. At the same time, the "Id_P" column in the "Orders" table is used to reference people in the "Persons" table, without the need to use their exact names. The "Id_P" column associates the two tables above.
Table creation statement:
Create table Persons (Id_P int, LastName varchar (255), FirstName varchar (255), Address varchar (255), City varchar (255 ));
ALTER TABLE Persons ADD CONSTRAINT pk_PersonID PRIMARY KEY (Id_P);
Create table Orders (Id_O int, OrderNo varchar (255), Id_P int );
ALTER TABLE Orders ADD CONSTRAINT pk_OrderID PRIMARY KEY (Id_O);
# Add a foreign key constraint
ALTER TABLE OrdersADD CONSTRAINT fk_PerOrdersFOREIGN KEY (Id_P)REFERENCES Persons(Id_P);
To revoke the foreign key constraint, use the following SQL statement:
MySQL:ALTER TABLE OrdersDROP FOREIGN KEY fk_PerOrders
SQL Server/Oracle/MS Access:ALTER TABLE OrdersDROP CONSTRAINT fk_PerOrders
Insert data:
insert into Persons values (1,'Adams','John','Oxford Street','London' );
INSERT INTO Persons VALUES (3,'Bush', 'George', 'Fifth Avenue', 'New York');
INSERT INTO Persons VALUES (2,'Gates', 'Bill', 'Changan Street', 'Beijing');
insert into orders values(1,'20150730100',2);
insert into orders values(2,'20150730100',3);
insert into orders values(3,'20150730100',2);
insert into orders values(4,'20150730100',3);
insert into orders values(5,'20150730100',3);
insert into orders values(6,'20150730100',1);
==============================================================================
3. SQL join instance
We can obtain data from two tables by referencing two tables: Who ordered the product and what products they ordered?
SELECT Persons.LastName, Persons.FirstName, Orders.OrderNoFROM Persons, OrdersWHERE Persons.Id_P = Orders.Id_P
You can also use the keyword JOIN to obtain data from two tables.
SELECT Persons.LastName, Persons.FirstName, Orders.OrderNoFROM PersonsINNER JOIN OrdersON Persons.Id_P = Orders.Id_PORDER BY Persons.LastName
Result set:
| LastName |
FirstName |
OrderNo |
| Adams |
John |
22456 |
| Adams |
John |
24562 |
| Carter |
Thomas |
77895 |
| Carter |
Thomas |
44678 |
Certificate --------------------------------------------------------------------------------------------------------------------------------------------------------
Copyright Disclaimer: This article is an original article by the blogger and cannot be reproduced without the permission of the blogger.
