SQL查询语句(三种查询).docVIP

连接查询 -------------------------------------------------------------------------------- 多个表联合起来查询方式 如table1和table2,table1作为左表，table2作为右表 连接类型分三种：（交叉连接，内连接，外连接） 1. 交叉连接（cross join）不带on子句，返回连接表中所有数据行的笛卡儿积 例：交叉连接查询customers表和orders表: select * from customers,orders; customers有5行记录，orders表有7行记录，查询结果共有35条记录 2. 内连接 （1）显式内连接,(inner join) 返回数据表中符合条件以及查询条件的数据行，使用inner join关键字。 Select c.id,o.customer_id,c.name,o.id order_id,order_number From customers c inner join orders o on c.id=o.customer_id; 查询结果： Id customer_id name order_id order_number 1 1 tom 1 Tom_order001 1 1 tom 2 Tom_order002 1 1 tom 3 Tom_order003 2 2 Mike 4 Mike_order001 3 3 Jack 5 Jack_order001 4 4 Linda 6 Linda_order001 （2）隐式内连接，不包含inner join关键字和on关键字，where中设定连接条件 Select c.id,o.customer_id,c.name,o.id order_id,order_number From customers c,orders o Where c.id=o.customer_id; 3. 外连接 （1）左外连接 ，使用left outer join关键字，在on子句中设定连接条件 Select c.id,o.customer_id,c.name,o.id order_id,order_number From customers c left outer join orders o on c.id=o.customer_id; 查询结果: Id customer_id name order_id order_number 1 1 tom 1 Tom_order001 1 1 tom 2 Tom_order002 1 1 tom 3 Tom_order003 2 2 Mike 4 Mike_order001 3 3 Jack 5 Jack_order001 4 4 Linda 6 Linda_order001 5 null Tom null null (2)带查询条件的左外连接查询,where中设定查询条件 Select c.id,o.customer_id,c.name,o.id order_id,order_number From customers c left outer join orders o on c.id=o.customer_id where o.id4 and c.id2; 查询结果： Id customer_id name order_id order_number 3 3