select 学习

关联表

CREATE TABLE class(no int primary key, class_name varchar(40));
INSERT INTO class VALUES(1,'初二(1)班');
INSERT INTO class VALUES(2,'初二(2)班');
INSERT INTO class VALUES(3,'初二(3)班');
INSERT INTO class VALUES(4,'初二(4)班');
CREATE TABLE student(no int primary key, student_name varchar(40), age int, class_no int);
INSERT INTO student VALUES(1, '张三', 14, 1);
INSERT INTO student VALUES(2, '吴二', 15, 1);
INSERT INTO student VALUES(3, '李四', 13, 2);
INSERT INTO student VALUES(4, '吴三', 15, 2);
INSERT INTO student VALUES(5, '王二', 15, 3);
INSERT INTO student VALUES(6, '李三', 14, 3);
INSERT INTO student VALUES(7, '吴二', 15, 4);
INSERT INTO student VALUES(8, '张四', 14, 4);

1、标准
SELECT student_name, class_name FROM student,
class
WHERE student.class_no = class.no;

2、distinct
select distinct student_name, class_no from student, class;

3、
select student_name, class_name from student, class;

oldboy=# select * from student a, class b where a.class_no=b.no and b.class_name='初二(2)班';
 no | student_name | age | class_no | no | class_name 
----+--------------+-----+----------+----+------------
  3 | 李四         |  13 |        2 |  2 | 初二(2)班
  4 | 吴三         |  15 |        2 |  2 | 初二(2)班
(2 rows)

oldboy=# explain analyze select * from student a, class b where a.class_no=b.no and b.class_name='初二(2)班';
                                                   QUERY PLAN                                                   
----------------------------------------------------------------------------------------------------------------
 Hash Join  (cost=17.79..35.25 rows=3 width=212) (actual time=0.020..0.022 rows=2 loops=1)
   Hash Cond: (a.class_no = b.no)
   ->  Seq Scan on student a  (cost=0.00..15.90 rows=590 width=110) (actual time=0.007..0.008 rows=8 loops=1)
   ->  Hash  (cost=17.75..17.75 rows=3 width=102) (actual time=0.008..0.008 rows=1 loops=1)
         Buckets: 1024  Batches: 1  Memory Usage: 9kB
         ->  Seq Scan on class b  (cost=0.00..17.75 rows=3 width=102) (actual time=0.005..0.006 rows=1 loops=1)
               Filter: ((class_name)::text = '初二(2)班'::text)
               Rows Removed by Filter: 3
 Planning Time: 0.080 ms
 Execution Time: 0.038 ms
(10 rows)

oldboy=# explain analyze select * from student where class_no in  (select no from class where class_name='初二(2)班');
                                                 QUERY PLAN                                                 
------------------------------------------------------------------------------------------------------------
 Hash Join  (cost=17.79..35.25 rows=3 width=110) (actual time=0.019..0.021 rows=2 loops=1)
   Hash Cond: (student.class_no = class.no)
   ->  Seq Scan on student  (cost=0.00..15.90 rows=590 width=110) (actual time=0.006..0.007 rows=8 loops=1)
   ->  Hash  (cost=17.75..17.75 rows=3 width=4) (actual time=0.007..0.008 rows=1 loops=1)
         Buckets: 1024  Batches: 1  Memory Usage: 9kB
         ->  Seq Scan on class  (cost=0.00..17.75 rows=3 width=4) (actual time=0.005..0.005 rows=1 loops=1)
               Filter: ((class_name)::text = '初二(2)班'::text)
               Rows Removed by Filter: 3
 Planning Time: 0.089 ms
 Execution Time: 0.038 ms
(10 rows)