在进行复杂数据查询时,我们往往需要使用到SQL语句中的连接查询。连接查询分为内连接和外连接,其中外连接又分为左连接和右连接,而本文将集中讨论左连接结合where条件的应用。
一、left join和where的基本用法
left join语句用于在两个或多个表之间建立联系,并将符合ON
条件的行组合在一起,即显示左表中的所有数据,如果右表中有符合关联条件的数据,则显示右表中的数据,否则用NULL填充。
SELECT A.*, B.* FROM tableA A LEFT JOIN tableB B ON A.key = B.key;
where条件用于从A和B表连接后的结果集中挑选符合条件的行。
SELECT A.*, B.* FROM tableA A LEFT JOIN tableB B ON A.key = B.key WHERE B.col1 = 'value1';
可以看到,以上where条件过滤的是右表,即查询结果集为从A表中取出所有数据,然后将符合ON
条件的数据和B表合并后,再在B表中过滤符合条件的数据,最终返回结果。
二、left join和where配合的问题
以MySQL为例,实际上left join和where的配合使用会造成效率的损耗。原因是,where条件应该在连接前进行过滤,而不是在连接后过滤。这样一来,在符合ON
条件前,就可以首先将不符合条件的数据过滤掉,减少连接后的数据量,进而提高查询效率。
以下两条查询语句可以帮助我们更好地理解这个问题。假设对于一个大小为A、B的表,其中A=100万,B=10万。
第一条语句如下:
SELECT A.*, B.* FROM tableA A LEFT JOIN tableB B ON A.key = B.key WHERE B.col1 = 'value1';
在查询过程中,将先很快地在B表中查找符合条件(即col1='value1'
)的记录,而B表的记录容量只有B=10万条,因此可以在很短时间内查找到目标记录。
第二条语句如下:
SELECT A.*, B.* FROM (SELECT * FROM tableA WHERE col1 = 'value1') A LEFT JOIN tableB B ON A.key = B.key;
在这种情况下,首先会在A表中过滤出符合条件(即col1='value1'
)的记录,此时A表的容量也只有A=100万中的一部分,然后再进行left join操作,这样可以大大减少需要进行join的数据量,提高查询速度。
三、使用left join on where进行优化
left join on where的用法是将where条件写在left join的ON条件中,从而实现在连接前进行过滤。
SELECT A.*, B.* FROM tableA A LEFT JOIN tableB B ON A.key = B.key AND B.col1 = 'value1';
这样一来,在连接之前就会过滤掉不符合B.col1 = 'value1'
的数据,避免了在连接后的大量数据中进行过滤的操作。实际测试也证明,left join on where的方法可以将查询语句的执行时间缩短数十倍。
结语
正确使用left join on where可以很大程度上提高SQL查询效率,从而缩短查询时间。尽管在MySQL 5.7及以上版本的一些情况下可以通过优化器进行优化,但我们仍然可以从左连接的基本用法、left join和where的配合问题,以及left join on where的优化三个方面来更好地理解和掌握该优化方法。