create table a (name varchar(50) ,id integer not null primary key );
insert into a values ('a-1',1);
insert into a values ('a-2',2);
insert into a values ('a-3',3);
insert into a values ('a-4',4);
insert into a values ('a-5',5);
create table b (bid integer not null primary key ,aid integer,bname varchar(50),bage integer,
CONSTRAINT fk_a_Id FOREIGN KEY (aid) REFERENCES a(id)
);
insert into b values (1,1,'b-1',15);
insert into b values (2,2,'b-1',117);
insert into b values (3,3,'b-1',123);
表b是表a的一个子表，通过aid与表a相关联，现想得到如下结果，求解正确的sql查询操作
name id bname bage
a-1 1 b-1 15
a-2 2 b-2 117
a-3 3 b-3 123
a-4 4
a-5 5
------解决方案--------------------
--create table a (name varchar(50) ,id integer not null primary key );
--insert into a values ('a-1',1);
--insert into a values ('a-2',2);
--insert into a values ('a-3',3);
--insert into a values ('a-4',4);
--insert into a values ('a-5',5);
--create table b (bid integer not null primary key ,aid integer,bname varchar(50),bage integer,
-- CONSTRAINT fk_a_Id FOREIGN KEY (aid) REFERENCES a(id)
--);
--insert into b values (1,1,'b-1',15);
--insert into b values (2,2,'b-1',117);
--insert into b values (3,3,'b-1',123);
SELECT A.NAME,A.ID,B.BNAME,B.BAGE
FROM A LEFT JOIN B ON A.ID=B.AID
/*
NAME ID BNAME BAGE
-------------------------------------------------- ----------- -------------------------------------------------- -----------
a-1 1 b-1 15
a-2 2 b-1 117
a-3 3 b-1 123
a-4 4 NULL NULL
a-5 5 NULL NULL