Left Join & Right Join & Full Join & Join

最新推荐文章于 2025-01-25 16:53:57 发布

最新推荐文章于 2025-01-25 16:53:57 发布 · 160 阅读

文章标签：

#SQL #MySQL

DB & SQL 专栏收录该内容

6 篇文章

订阅专栏

本文详细介绍了SQL中不同连接类型的特点及使用场景，包括左连接、右连接、全连接及内连接，并通过具体示例展示了如何在实际应用中选择合适的连接方式。

Foreword: TBL_BPM_HK_PINS save house keep process instances(contains all outdated pins and uptodate pins), TBL_BPM_PMAP save process maps. Maybe the process map has been deleted, so if want to provider a page to manage these pins, cannot use the common join, or there will be some pins cannot be searched.
Use left join instead.


select m.PMAP_ID, m.PMAP_NAME, m.PMAP_VERSION, i.PINS_ID, i.PINS_STATUS, i.CREATED_BY, i.CREATED_DT, i.UPDATED_DT
from TBL_BPM_PMAP m right join TBL_BPM_HK_PINS i on i.PMAP_ID = m.PMAP_ID
where i.PINS_STATUS like '%status%'
      and m.PMAP_NAME like '%process_map_name%'

The difference of several join mode.
Example:
Table1, City(cityId,cityName)
Table2, Company(companyId,companyName,cityId)
[color=blue]1. Left (Out) Join[/color]
returns ALL CITIES no matter whether existing a company in this city.


SELECT ct.cityId, ct.cityName, cp.companyName
FROM City ct LEFT JOIN Company cp ON ct.cityId = cp.cityId

[color=blue]2. Right (Out)Join[/color]
returns ALL COMPANIES no matter whether this company located where.


SELECT cp.companyId, cp.companyName, ct.cityName
FROM City ct RIGHT JOIN Company cp ON ct.cityId = cp.cityId

[color=blue]3. Full (Out) Join[/color]
returns ALL CITY'names and COMPANY'names.


SELECT ct.cityName, cp.companyName
FROM City ct FULL JOIN Company cp ON ct.cityId = cp.cityId

Note: Mysql dosen't support full join operation. Use below instead.

SELECT ct.cityName, cp.companyName
FROM City ct LEFT JOIN Company cp ON ct.cityId = cp.cityId
union
SELECT  ct.cityName, cp.companyName
FROM City ct RIGHT JOIN Company cp ON ct.cityId = cp.cityId

[color=blue]4. (Inner) Join[/color]
return the city'name and the company'name if the company loacted on this city.


SELECT ct.cityName, cp.companyName
FROM City ct JOIN Company cp ON ct.cityId = cp.cityId