left join on and with the left join on where the difference between sharing

2011-05-08  来源:本站原创  分类:Database  人气:100 

By connecting two or more database tables to return the records, will generate a middle of the temporary table, then this temporary table to return to the user.

When using the left jion in, on, and where the difference between the conditions are as follows:

1, on condition that generated the conditions for the use of temporary table, it does not matter whether the conditions on the true, will return to the left of the table records.

2, where conditions are better in the temporary table generated, then the temporary table to filter conditions. At this time has no meaning left join (must return on the left of the table records), the condition is not true for all filtered out.

Suppose there are two tables:

Table 1 tab1:

id size

110

2 20

3 30

Table 2 tab2:

size name

10 AAA

20 BBB

20 CCC

Two SQL:
1, select * form tab1 left join tab2 on (tab1.size = tab2.size) where tab2.name = 'AAA'
2, select * form tab1 left join tab2 on (tab1.size = tab2.size and tab2.name = 'AAA')

The first SQL process:

1, the middle table
on condition:
tab1.size = tab2.size

tab1.id tab1.size tab2.size tab2.name

1 10 10 AAA

2 20 20 BBB

2 20 20 CCC

3 30 (null) (null)

2, and then the middle of the table filter
where conditions:
tab2.name = 'AAA'

tab1.id tab1.size tab2.size tab2.name

1 10 10 AAA

The second SQL process:

1, the middle table
on condition:
tab1.size = tab2.size and tab2.name = 'AAA'
(Conditions are not true will return the records in the left table)

tab1.id tab1.size tab2.size tab2.name

1 10 10 AAA

2 20 (null) (null)

3 30 (null) (null)

In fact, the key reason for these results is left join, right join, full join are unique, regardless of whether the conditions on the true left or right will return records in the table, full with the left and right are the properties and set. The inner jion not the particularity of the conditions placed on in and where in the returned result set is the same.

相关文章
  • About the database using the left join on ... and ... and the left join on .... where ... different, and use group by to pay attention to the situation 2010-04-02

    About the database using the left join on ... and ... and the left join on .... where ... different, and use group by to pay attention to the situation create table UserAccount ( UID int, UName varchar (50), JoinDate datetime ) insert into UserAccoun

  • Left in the database connection (left join) and the right connections (right join) difference 2010-11-01

    Left Join / Right Join / inner join related Connect the left and right connections on the concluding sentence: Left connected to the right table where only the shadow, affects only the left where the right connections form. Left Join select * from tb

  • left join on and with the left join on where the difference between sharing 2011-05-08

    By connecting two or more database tables to return the records, will generate a middle of the temporary table, then this temporary table to return to the user. When using the left jion in, on, and where the difference between the conditions are as f

  • Left in the database connection (left join) and the right connections (right join) the difference 2010-11-01

    Left Join / Right Join / inner join relevant Connect the left and right connections on concluding sentence: Left join where only shadow to the right table, where only affect the right to connect the left table. Left Join select * from tbl1 Left Join

  • left join on and with the left join on where the difference between the share 2011-05-08

    Database by connecting two or more tables to return records, will generate an intermediate temporary table, then this temporary table to the user. When using the left jion in, on and where the difference between the conditions are as follows: 1, on c

  • join method of understanding 2010-03-27

    Join API in the description of the method is as follows: Quote void join () Waiting for the thread to terminate. //join Method of understanding import static java.lang.System.out; class M020 { public static void main(String[] args) throws Exception {

  • oracle outer join query to connect all connections within the connection 2010-03-12

    oracle of the connection can be divided into, in connection (inner join), outer join (outer join), fully connected (full join), not only Oracle, the database also have many other these three kinds of ways to connect query In connection inner join, th

  • sql the difference between left join.right join.inner join 2010-03-24

    left join (left join) returns include all records from left table and right table in the same field records join right join (the right link), including the right to return all the records in the table and left the table in the join fields are equal r

  • oracle multi table join 2010-03-24

    Multi-table join: Query a data source is no longer a single table, we could extract more than one table of data associated with, for example, and dept from emp table to find out where the name of the department staff over ,---- table to the joint inq

  • The left join sql command Introduction 2010-03-31

    Address: http://www.phpweblog.net/zwws/archive/2007/02/23/944.html To a popular interpretation of it. Cases of the table a aid adate 1 a1 2 a2 3 a3 Table b bid bdate 1 b1 2 b2 4 b4 Two tables a, b connected to the same field of id out select * from a

  • The left join.right join.inner join sql difference (change) 2010-02-24

    left join (left join) returns include all records from left table and right table in the same field records join right join (the right link), including the right to return all the records in the table and left the table in the join fields are equal r

  • Java multi-threaded sleep (), join (), interrupt (), wait (), notify () 2010-04-10

    1. Sleep () & interrupt () Thread A is using sleep () to suspend the: Thread.sleep (100000); If you wait to cancel his state of being executed in the thread (like here B) call a.interrupt (); A thread made to give up sleep operation, where a is the t

  • Sql join a comprehensive analysis of keywords! 2010-04-11

    SQL join is used by two or more tables in the relationship between the columns, query the data from these tables. Join and Key Sometimes, in order to get complete results, we need two or more tables to obtain the results. We need to do join. Database

  • [Change] left join summary 2010-04-19

    EG1: popular about A left join B of the connection table records the number of records with A with A right join B of the connection table records the number of records with the B with A left join B is equivalent to B right join Atable A: Field_K , Fi

  • Java Thread join method in 2010-04-21

    The morning of the interview time and interviewer then asked what things like SSH is not clear. Then chatted about the thread, said join, and I said, is to join a combined meaning, from the Internet to see, huh, huh. But this argument is also more ac

  • Oracle SQL statement (join queries) 2010-04-22

    1, internal connections and external connections within the connection <br /> return to satisfy the connection conditions for the record; suits the connection is connected within the extension, which will not only satisfy the connection conditions o

  • Java multi-threaded sleep (), join (), interrupt (), wait (), notify () (turn) 2010-05-08

    Knowledge of Java multi-threading can take a look at "Thinking in Java" in the multi-threaded part and the "Java Network Programming" in Chapter 5 of part of the multi-threaded The following is a reference <<Java Multithreaded mo

  • Mysql the inner join, left join, right join Xiangjie 2010-05-21

    First of all, to borrow under the official explanation: inner join (the equivalent connection): return only join two tables in the same field line; left join (left join): Back to the table, including all the records left and right join table records

  • MYSQl left join query efficiency of joint 2010-05-25

    user table: id | name --- 1 | libk 2 | zyfon 3 | daodao user_action table: user_id | action ----- 1 | jump 1 | kick 1 | jump 2 | run 4 | swim sql: select id, name, action from user as u left join user_action a on u.id = a.user_id result: id | name |

  • the type of table join 2010-06-25

    [B] join: [/ b] return rows at least one match in tables inner join is the most common join type used in applications, and can be regarded the default join type outer join [u] [/ u]: outer join does not requires each record in the two records, outer