mysql中join的七种用法
内连接
inner join:
SELECT * FROM hotel h INNER JOIN room r ON h.id = r.hotelid
左外连接
left join:
SELECT * FROM hotel h LEFT OUTER JOIN room r ON h.id = r.hotelid
右外连接
right join:
SELECT * FROM hotel h RIGHT OUTER JOIN room r ON h.id = r.hotelid
左连接
SELECT * FROM hotel h LEFT JOIN room r ON h.id = r.hotelid WHERE r.hotelid IS NULL
右连接
SELECT * FROM hotel h RIGHT JOIN room r ON h.id = r.hotelid WHERE r.hotelid IS NULL
全连接
SELECT * FROM hotel h RIGHT JOIN room r ON h.id = r.hotelid UNION
SELECT * FROM hotel h LEFT JOIN room r ON h.id = r.hotelid
两张表中均没有出现的数据集
SELECT * FROM hotel h RIGHT JOIN room r ON h.id = r.hotelid WHERE h.id IS NULL
UNION SELECT * FROM hotel h LEFT JOIN room r ON h.id = r.hotelid WHERE r.hotelid IS NULL