Database table join

You can retrieve data from two or more tables based on the logical relationship between tables. Connection query is an important feature of relational databases. It is also a major indicator that distinguishes it from other types of database management systems. The syntax format of the join uses the from clause to join from first_table join_type second_table [ON (join_condition)]

• Join_type: the type of join executed. It can be divided into cross join, internal join, and external join.
• Join_condition: used to specify the join Condition

Join with WHERE clause

From fist_table, second_table where (join_condition)

• It is the syntax format specified by ansi SQL: 1989. Join tables are separated by commas, but only cross-join and internal join are supported.

Cross join is the first stage of join query. It performs Cartesian Product on two tables. Join each row in the first table to all rows in the second table. Therefore, the size of the generated result set is equal to the number of rows in the first table multiplied by the number of rows in the second table. Example Table creation statement

DROP TABLE IF EXISTS `employees`;CREATE TABLE `employees` (`empid` int(11) NOT NULL AUTO_INCREMENT,`emp_name` varchar(100) NOT NULL,PRIMARY KEY (`empid`)) ENGINE=MyISAM;INSERT INTO employees(emp_name)VALUES ("wangzhengyi"),("chenshan"),("lulu");DROP TABLE IF EXISTS `orders`;CREATE TABLE `orders` (`oid` int(11) NOT NULL AUTO_INCREMENT,`empid` int(11) NOT NULL,`season` varchar(100) NOT NULL,`money` int(11) NOT NULL,PRIMARY KEY (`oid`)) ENGINE=MyISAM;INSERT INTO orders(empid,season,money)VALUES ("1","season 1",2000),("1","season 2",2000),("1","season 2",2000),("2","season 1",1000),("2","season 2",1000);

Example query statement

select * from employees cross join orders;

Inner join is a typical join operation that uses comparison operators such as =, <> to perform conditional join. The internal join uses the first two stages of the join query, namely Cartesian Product and on filter. Internal join only obtains the record internal join syntax format that matches the join condition in two tables.

• Select * From Table1 inner join Table2 on table1.column1 = table2.column1; (the inner keyword can be omitted. If this keyword is omitted, it will be an internal join by default)

Equivalent for internal join non-equivalent for internal join currently no non-equivalent internal link has been used in the project, not to mention the unfinished SQL statement to be continued today, suddenly, the table connection is a little unfamiliar. I reviewed the basic knowledge and referred to <sharp SQL>. However, due to the time relationship and the current point of interest, it is not the SQL statement, it's just a rough introduction and will be further improved in the future.