Oracle--left the difference between join and and left join where

Development program, often encounter the left Join,inner join statement, join is a relational database system one of the important operations, relatively faster, so people generally prefer to choose join statement.

However, some of the usages of joins are not necessarily clear when you are doing a program. Today we're going to talk about the left join and and leave join where.

? When a database returns records by connecting two or more tables, an intermediate temporary table is generated, and the temporary table is returned to the user.

When using left jion on, the and and where conditions differ as follows:

1. The on condition is the condition used when generating a temporary table, which returns records from the table on the left regardless of whether the condition on is true or not, and only filters out records in Table B. All non-qualifying parts in table B are all set to NULL.

2. Where condition is the condition that the temporary table is filtered after the temporal table has been generated. At this point there is no left join meaning (must return the record of the table on the right), the condition is not true all filter out.

Example:

To build a table statement:

CREATE TABLE Tmp_lxq_1
(
ID VARCHAR2 (10),
Name VARCHAR2 (20)
);
INSERT INTO Tmp_lxq_1
Select ' 1 ', ' Zhang San ' from dual;
INSERT INTO Tmp_lxq_1
Select ' 2 ', ' John Doe ' from dual;
INSERT INTO Tmp_lxq_1
Select ' 3 ', ' Harry ' from dual;
Commit

Tmp_lxq_1 Table Results:

ID NAME
1 sheets of three
2 John Doe
3 Harry

drop table tmp_lxq_2;
CREATE TABLE Tmp_lxq_2
(
ID VARCHAR2 (10),
Subject VARCHAR2 (30),
Score Varchar2 (30)
);

INSERT INTO Tmp_lxq_2
Select ' 1 ', ' language ', ' dual ' from;
INSERT INTO Tmp_lxq_2
Select ' 2 ', ' math ', ' all ' from dual;
INSERT INTO Tmp_lxq_2
Select ' 4 ', ' English ', ' the ' from dual;
Commit

Tmp_lxq_2 results:

ID SUBJECT Score
1 Languages 80
2 Mathematics 90
4 English 60

Then run the following statements:

1.inner Join

Inner Jion the intersection of Table A and table B, regardless of whether the a table is left or the B table is left.

Select A.id,a.name,b.id,b.subject,b.score from Tmp_lxq_1 a
INNER JOIN Tmp_lxq_2 b
On A.id=b.id;

Results:

ID NAME ID SUBJECT score
1 31 Languages 80
2 Li 42 Mathematics 90

2.left Join

The left join is based on the records of Table A, a can be regarded as the right table, and B can be regarded as left table. In other words, the records of the left table (A) will all be represented, and the right table (B) will only display records that match the search criteria (in the example: a.ID = b.id). The low-record of table B is null.

Select A.id,a.name,b.id,b.subject,b.score from Tmp_lxq_1 a
Left JOIN Tmp_lxq_2 b
On A.id=b.id;

1 31 Languages 80
2 Li 42 Mathematics 90
3 Harry

3.left Join and

The left join and is also based on the records of a table, a can be regarded as the table, B can be regarded as the right table, and left join and is based on left table. In other words, the records of the left table (A) will all be represented, and the right table (B) will only display records that match the search criteria (in the example: a.ID = b.id). The table B records are NULL, plus and conditions, the A-table records will all be represented, and B will only show the qualifying records, B table records in the non-qualifying places are displayed as null.

Select A.id,a.name,b.id,b.subject,b.score from Tmp_lxq_1 a
Left JOIN Tmp_lxq_2 b
On A.id=b.id
and b.score>=80;

ID NAME ID SUBJECT score
1 31 Languages 80
2 Li 42 Mathematics 90
3 Harry

4.left Join where

The LEFT join where condition is the condition that the temporary table is filtered after the staging table has been generated. At this point there is no left join meaning (must return the record of the table on the right), the condition is not true all filter out.

Select A.id,a.name,b.id,b.subject,b.score from Tmp_lxq_1 a
Left JOIN Tmp_lxq_2 b
On A.id=b.id
where b.score>=80;

ID NAME ID SUBJECT score
1 31 Languages 80
2 Li 42 Mathematics 90

Oracle--left the difference between join and and left join where