Database Query internal and external connections

Createtabletmp3 (ageint, namechar (100) GOcreatetabletmp3 {ageint, madechar (100)} GOwww.2cto. cominsertintotmp1values (10, liwei) insertintotmp1values (11, lijing) insertintotmp1values (12, lijia) insert

Create table tmp3 (age int, name char (100) GO create table tmp3 {age int, made char (100)} GO www.2cto.com insert into tmp1 values (10, liwei) insert into tmp1 values (11, lijing) insert into tmp1 values (12, lijia) insert

Database Query internal and external connections

Create table tmp3

(

Age int,

Name char (100)

)

GO

Create table tmp3

{

Age int,

Made char (1, 100)

}

GO

Www.2cto.com

Insert into tmp1 values (10, 'liwei ')

Insert into tmp1 values (11, 'lijing ')

Insert into tmp1 values (12, 'lijia ')

Insert into tmp3 values (10, 'liwei ')

Insert into tmp3 values (12, 'lijing ')

Insert into tmp3 values (14, 'lija ')

-- Inner join: delete all rows with no matching rows from other connected tables.

Select * from tmp1, tmp3 where tmp1.age = tmp3.age

Select * from tmp1 join tmp3 on tmp1.age = tmp3.age

Age name

10 liwei 10 liwei

12 lijia 12 lijing

-- Left Outer Join: All rows in the first table are retained, but only the rows matching the second table and the first table are retained. The empty rows in the second table are put into NULL values.

Select * from tmp1 left join tmp3 on tmp1.age = tmp3.age

10 liwei 10 liwei

11 lijing NULL

12 lijia 12 lijing

-- Right outer join: All rows in the second table are retained, but only the rows matching the first table and the second table are contained. The corresponding row in the first table is put into NULL values.

Select * from tmp1 right join tmp3 on tmp1.age = tmp3.age

10 liwei 10 liwei

12 lijia 12 lijing

NULL 14 lijia

Www.2cto.com

-- Full outer join: Display All rows in the two tables in the result table Zhou suhong, and match as many data and connection conditions as possible.

Select * from tmp1 full join tmp3 on tmp1.age = tmp3.age

10 liwei 10 liwei

12 lijia 12 lijing

NULL 14 lijia

11 lijing NULL

-- Cross join (Cartesian Product): Records of two tables are combined. There cannot be an on condition.

Select * from tmp1 cross join tmp3

10 liwei 10 liwei

11 lijing 10 liwei

12 lijia 10 liwei

10 liwei 12 lijing

11 lijing 12 lijing

12 lijia 12 lijing

10 liwei 14 lijia

11 lijing 14 lijia

12 lijia 14 lijia

Create table student

(

Student_no int,

Name char (100 ),

Group_no int

)

Insert into student values (100, 'liwei', 99)

Insert into student values (101, 'lijia ', 100)

Insert into student values (102, 'lijing', 101)

Www.2cto.com

-- Auto join: the same table itself is connected to itself. If you need the information of two different rows in a table at the same time (generally, each database operation only operates on multiple columns in one row), you need to connect the table to itself. To better understand the connection, you can think of a table as two independent tables. In the from clause, the table is listed twice.

Select a. student_no, a. name, B. student_no as leader number, B. name as leader name from student a, student B where a. group_no = B. student_no

Student_no name team leader no. Team Leader name

101 lijia 100 liwei

102 lijing 101 lijia