conclusion
From the following test results, it is easy to see that the left-related query statement will be transformed into an internal correlation as long as there is a WHERE filter condition.
1. Creating tables
Demonstrate the use of random creation of two tables
--Class table CREATE TABLE T_CLASS( class_id NUMBER not null, class_name VARCHAR2(100) ) --Student list CREATE TABLE T_STUDENT( student_id NUMBER not null, class_id NUMBER not null, student_name VARCHAR2(100), age NUMBER, sex CHAR(1) )
2. Creating data
--Class data insert into T_CLASS (CLASS_ID, CLASS_NAME) values (1, 'Class one'); insert into T_CLASS (CLASS_ID, CLASS_NAME) values (2, 'Class two'); insert into T_CLASS (CLASS_ID, CLASS_NAME) values (3, 'Class three'); insert into T_CLASS (CLASS_ID, CLASS_NAME) values (4, 'Class four'); insert into T_CLASS (CLASS_ID, CLASS_NAME) values (5, 'the fifth class'); --Student data insert into T_STUDENT (STUDENT_ID, CLASS_ID, STUDENT_NAME, AGE, SEX) values (1, 1, 'Lee 1', 3, '1'); insert into T_STUDENT (STUDENT_ID, CLASS_ID, STUDENT_NAME, AGE, SEX) values (2, 1, 'Lee 2', 2, '1'); insert into T_STUDENT (STUDENT_ID, CLASS_ID, STUDENT_NAME, AGE, SEX) values (3, 1, 'Lee 3', 3, '1'); insert into T_STUDENT (STUDENT_ID, CLASS_ID, STUDENT_NAME, AGE, SEX) values (4, 2, 'Lee 4', 4, '1'); insert into T_STUDENT (STUDENT_ID, CLASS_ID, STUDENT_NAME, AGE, SEX) values (5, 2, 'Lee 5', 3, '2'); insert into T_STUDENT (STUDENT_ID, CLASS_ID, STUDENT_NAME, AGE, SEX) values (6, 2, 'Lee 6', 3, '1'); insert into T_STUDENT (STUDENT_ID, CLASS_ID, STUDENT_NAME, AGE, SEX) values (7, 3, 'Lee 7', 6, '2'); insert into T_STUDENT (STUDENT_ID, CLASS_ID, STUDENT_NAME, AGE, SEX) values (8, 3, 'Lee 8', 4, '2'); insert into T_STUDENT (STUDENT_ID, CLASS_ID, STUDENT_NAME, AGE, SEX) values (9, 2, 'Lee 9', 2, '2'); insert into T_STUDENT (STUDENT_ID, CLASS_ID, STUDENT_NAME, AGE, SEX) values (10, 2, 'Lee 10', 3, '1'); insert into T_STUDENT (STUDENT_ID, CLASS_ID, STUDENT_NAME, AGE, SEX) values (11, 3, 'Lee 11', 3, '2'); insert into T_STUDENT (STUDENT_ID, CLASS_ID, STUDENT_NAME, AGE, SEX) values (12, 2, 'Lee 12', 8, '2'); insert into T_STUDENT (STUDENT_ID, CLASS_ID, STUDENT_NAME, AGE, SEX) values (13, 1, 'Lee 13', 6, '2');
3. Several Cases of Left Relevance
1. No filtering condition
Query statement:
SELECT * FROM T_CLASS A LEFT JOIN T_STUDENT B ON A.CLASS_ID = B.CLASS_ID ORDER BY A.CLASS_ID;
Query results:
2. The filtering condition of table A in AND
Query statement:
SELECT * FROM T_CLASS A LEFT JOIN T_STUDENT B ON A.CLASS_ID = B.CLASS_ID AND A.CLASS_ID = 1 ORDER BY A.CLASS_ID;
Query results:
3.A Table Filtering Conditions in WHERE
Query statement:
SELECT * FROM T_CLASS A LEFT JOIN T_STUDENT B ON A.CLASS_ID = B.CLASS_ID WHERE A.CLASS_ID = 1 ORDER BY A.CLASS_ID;
Query results:
4. The filter condition of table B in AND
Query statement:
SELECT * FROM T_CLASS A LEFT JOIN T_STUDENT B ON A.CLASS_ID = B.CLASS_ID AND B.AGE = 3 ORDER BY A.CLASS_ID;
Query results:
5.B Table Filtration Conditions in WHERE
Query statement:
SELECT * FROM T_CLASS A LEFT JOIN T_STUDENT B ON A.CLASS_ID = B.CLASS_ID WHERE B.AGE = 3 ORDER BY A.CLASS_ID;
Query results:
6. The filtering condition of table A is in AND, and that of table B is in WHERE.
Query statement:
SELECT * FROM T_CLASS A LEFT JOIN T_STUDENT B ON A.CLASS_ID = B.CLASS_ID AND A.CLASS_ID = 1 WHERE B.AGE = 3 ORDER BY A.CLASS_ID;
Query results:
7. The filter condition of table A is in WHERE, and that of table B is in AND.
Query statement:
SELECT * FROM T_CLASS A LEFT JOIN T_STUDENT B ON A.CLASS_ID = B.CLASS_ID AND B.AGE = 3 WHERE A.CLASS_ID = 1 ORDER BY A.CLASS_ID;
Query results:
8. The filtering conditions of tables A and B are in AND.
Query statement:
SELECT * FROM T_CLASS A LEFT JOIN T_STUDENT B ON A.CLASS_ID = B.CLASS_ID AND B.AGE = 3 AND A.CLASS_ID = 1 ORDER BY A.CLASS_ID;
Query results:
9.A and B tables have filtering conditions in WHERE
Query statement:
SELECT * FROM T_CLASS A LEFT JOIN T_STUDENT B ON A.CLASS_ID = B.CLASS_ID WHERE B.AGE = 3 AND A.CLASS_ID = 1 ORDER BY A.CLASS_ID;
Query results:
Attached below are all query statements:
--1.No filtering condition SELECT * FROM T_CLASS A LEFT JOIN T_STUDENT B ON A.CLASS_ID = B.CLASS_ID ORDER BY A.CLASS_ID; --2.A Table filtering conditions are as follows AND in SELECT * FROM T_CLASS A LEFT JOIN T_STUDENT B ON A.CLASS_ID = B.CLASS_ID AND A.CLASS_ID = 1 ORDER BY A.CLASS_ID; --3.A Table filtering conditions are as follows WHERE in SELECT * FROM T_CLASS A LEFT JOIN T_STUDENT B ON A.CLASS_ID = B.CLASS_ID WHERE A.CLASS_ID = 1 ORDER BY A.CLASS_ID; --4.B Table filtering conditions are as follows AND in SELECT * FROM T_CLASS A LEFT JOIN T_STUDENT B ON A.CLASS_ID = B.CLASS_ID AND B.AGE = 3 ORDER BY A.CLASS_ID; --5.B Table filtering conditions are as follows WHERE in SELECT * FROM T_CLASS A LEFT JOIN T_STUDENT B ON A.CLASS_ID = B.CLASS_ID WHERE B.AGE = 3 ORDER BY A.CLASS_ID; --6.A Table filtering conditions are as follows AND Medium, B Table filtering conditions are as follows WHERE in SELECT * FROM T_CLASS A LEFT JOIN T_STUDENT B ON A.CLASS_ID = B.CLASS_ID AND A.CLASS_ID = 1 WHERE B.AGE = 3 ORDER BY A.CLASS_ID; --7.A Table filtering conditions are as follows WHERE Medium, B Table filtering conditions are as follows AND in SELECT * FROM T_CLASS A LEFT JOIN T_STUDENT B ON A.CLASS_ID = B.CLASS_ID AND B.AGE = 3 WHERE A.CLASS_ID = 1 ORDER BY A.CLASS_ID; --8.A Table and B Table filtering conditions are all in AND in SELECT * FROM T_CLASS A LEFT JOIN T_STUDENT B ON A.CLASS_ID = B.CLASS_ID AND B.AGE = 3 AND A.CLASS_ID = 1 ORDER BY A.CLASS_ID; --9.A Table and B Table filtering conditions are all in WHERE in SELECT * FROM T_CLASS A LEFT JOIN T_STUDENT B ON A.CLASS_ID = B.CLASS_ID WHERE B.AGE = 3 AND A.CLASS_ID = 1 ORDER BY A.CLASS_ID;