Menu
Sql statement.
1.select a.* from A a left join B b on a.id =b.id and a.id=2; 2.select a.* from A a left join B b on a.id =b.id where a.id=2; what is the difference of this two sql statement?
244
When you use a Left Outer join without an On or Where clause, there is no difference between the On and Where clause. Both produce the same result as in the following. First we see the result of the left join using neither an On nor a Where clause.
Left join is applied to the tables Course and Student and the table below is the result set. The left table and its corresponding matching rows on the right table are displayed. If a user wants to display the rows only in the left table, where clause can be used in the query.
If the tables involved in the join operation are too small, say they have less than 10 records and the tables do not possess sufficient indexes to cover the query, in that case, the Left Join is generally faster than Inner Join. As you can see above, both the queries have returned the same result set.
To use the WHERE clause to perform the same join as you perform using the INNER JOIN syntax, enter both the join condition and the additional selection condition in the WHERE clause. The tables to be joined are listed in the FROM clause, separated by commas. This query returns the same output as the previous example.
create table A(id int); create table B(id int); INSERT INTO A VALUES(1); INSERT INTO A VALUES(2); INSERT INTO A VALUES(3); INSERT INTO B VALUES(1); INSERT INTO B VALUES(2); INSERT INTO B VALUES(3); SELECT * FROM A; SELECT * FROM B; id ----------- 1 2 3 id ----------- 1 2 3 Filter on the JOIN to prevent rows from being added during the JOIN process.
select a.*,b.* from A a left join B b on a.id =b.id and a.id=2; id id ----------- ----------- 1 NULL 2 2 3 NULL WHERE will filter after the JOIN has occurred.
select a.*,b.* from A a left join B b on a.id =b.id where a.id=2; id id ----------- ----------- 2 2 If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
