MySQL connection query

A: New two sheets

Student table

CREATE TABLE Student (

Idint PRIMARY KEY auto_increment,

Snamevarchar () Not NULL

);

SELECT * from student;

INSERT into student VALUES (1, ' Zhang San '), (2, ' John Doe '), (3, ' Harry ');

Course Table

CREATE TABLE Course (

Idint PRIMARY KEY,

Cnamevarchar () Not NULL

);

SELECT * from course;

INSERT into Course VALUES (1, ' Football '), (2, ' Music '), (4, ' art ');

two : External Connection

The outer connection can be divided into: Left join, right connection, complete outer connection.

1. Left connection, or ieft outer join

SELECT * FROM student left JOIN course onstudent.id=course.id;

ID sname ID CNAME

1 sheets of 31 football

2 Li 42 Music

3 Harry \ n

The left OUTER join contains all the rows from the left table in a left join, and if there is no match on the right table in the table, the portion of the right table in the corresponding row in the result is all empty (null).

Note: We cannot say at this point that the resulting row count equals the number of rows in the left table data. Of course, the number of rows in this query result is equal to the number of rows in the left table data, because the left and right tables are one-to-one.

2. Right-connect to the join on either or outer join

SELECT * FROM student right JOIN course onstudent.id=course.id;

ID sname ID CNAME

1 sheets of 31 football

2 Li 42 Music

\ n \ 4 Art

The right outer join contains all the rows in the right table, and if a row in the left table does not match on the right table, the portion of the corresponding left table in the result is all empty (null).

Note: Also at this point we cannot say that the resulting row count equals the number of rows in the right table. Of course, the number of rows in this query result is equal to the number of rows in the left table data, because the left and right tables are one-to-one.

2. fully out -of-band join or full outer join

SELECT * FROM student full JOIN course;

IDsnameIDcname

1 sheets of 31 football

2 Li 41 Football

3 Kings 51 Football

1 Piece of 32 music

2 Li 42 Music

3 Kings 52 Music

1 Sheets of 34 art

2 Li 44 Art

3 Kings 54 Art

Three: Inner connection Join or INNER JOIN

SELECT * FROM student INNER JOIN course onstudent.id=course.id;

Idsnameidcname

1 sheets of 31 football

2 Li 42 Music

Inner JOIN is a comparison operator that returns only rows that meet the criteria.

This is equivalent to: SELECT * from Student,course where student.id=course.id

Quad: Cross Join

SELECT * From student cross JOIN course;

Idsnameidcname

1 sheets of 31 football

2 Li 41 Football

3 Kings 51 Football

1 Piece of 32 music

2 Li 42 Music

3 Kings 52 Music

1 Sheets of 34 art

2 Li 44 Art

3 Kings 54 Art

If we add a WHERE clause to this SQL at this time such as Sql:select * from Student cross join course Wherestudent.id=course.id

The result set that matches the condition is returned, and the result is the same as the inner join shows.

Copyright Notice: Bo Master original articles, reproduced please indicate the source. Http://blog.csdn.net/dzy21

MySQL connection query