sql example 10 -- join

table worker ( wid int auto_increment primary key, fname varchar(20) not null, lname varchar(20) not null ); create table job ( jid int not null auto_increment primary key, job_name varchar(20) not null ); insert into worker (fname, lname) values (‘a‘, ‘a‘), (‘b‘, ‘b‘), (‘c‘, ‘c‘); insert into job (job_name) values (‘cleaner‘), (‘farmer‘), (‘boss‘);

• join
  

  select w.fname, w.lname, j.job_name from worker w join job j;
  

  | fname | lname | job_name |
  +-------+-------+----------+
  | a     | a     | cleaner  |
  | b     | b     | cleaner  |
  | c     | c     | cleaner  |
  | a     | a     | farmer   |
  | b     | b     | farmer   |
  | c     | c     | farmer   |
  | a     | a     | boss     |
  | b     | b     | boss     |
  | c     | c     | boss     |
  

  select w.fname, w.lname, j.job_name from worker w join job j where w.wid = j.jid;
  

  | fname | lname | job_name |
  +-------+-------+----------+
  | a     | a     | cleaner  |
  | b     | b     | farmer   |
  | c     | c     | boss     |
  

  insert into worker (fname, lname) values (‘d‘, ‘d‘);
  

  select w.fname, w.lname, j.job_name from worker w join job j where w.wid = j.jid;
  

  | fname | lname | job_name |
  +-------+-------+----------+
  | a     | a     | cleaner  |
  | b     | b     | farmer   |
  | c     | c     | boss     |
  

• left join
  待续, 先占个位置 <2015-06-14 20:57>
  
• right join
  待续, 先占个位置 <2015-06-14 20:57>
  

sql example 10 -- join

标签：