-- 外连接:
select u.*, o.org_name from user u left join org o on u.org_id = o.org_id;
-- 子查询:
select u.*, (select o.org_name from org o where u.org_id = o.org_id) from user u;
这两个比较,性能应该是外连接的高吧?(上面两个id皆为主键)(注:因为在Hibernate中,没做关联映射,是不能使用外连接的,但可以使用子查询,故想问下比较下两者性能)
一般情况下,使用表连接比EXISTS更高效
-->低效:
SELECT *
FROM employees e
WHERE EXISTS
(SELECT 1
FROM departments
WHERE department_id = e.department_id AND department_name = 'IT');
-->高效:
SELECT * -->经测试此写法SQLplus下比上面的写法多一次逻辑读,而在Toad下两者结果一致
FROM employees e INNER JOIN departments d ON d.department_id = e.department_id
WHERE d.department_name = 'IT';