test/sql/Join/exp.test012.ksh

   1 echo create table emp(eno int,mgrno int,sal float,deptno int);
   2 Statement Executed
   3 echo insert into emp values(1,3,5000,10);
   4 Statement Executed: Rows Affected = 1
   5 echo insert into emp values(2,4,4000,20);
   6 Statement Executed: Rows Affected = 1
   7 echo insert into emp values(3,NULL,8000,10);
   8 Statement Executed: Rows Affected = 1
   9 echo create table dept(deptno int,dname char(10),lid int);
  10 Statement Executed
  11 echo insert into dept values(10,'IT',100);
  12 Statement Executed: Rows Affected = 1
  13 echo insert into dept values(20,'HR',300);
  14 Statement Executed: Rows Affected = 1
  15 echo create table loc(lid int,state char(10));
  16 Statement Executed
  17 echo insert into loc values(100,'Orissa');
  18 Statement Executed: Rows Affected = 1
  19 echo insert into loc values(200,'Cal');
  20 Statement Executed: Rows Affected = 1
  21 echo insert into loc values(300,'Calif');
  22 Statement Executed: Rows Affected = 1
  23 echo select * from emp;
  24 ---------------------------------------------------------
  25         emp.eno emp.mgrno       emp.sal emp.deptno
  26 ---------------------------------------------------------
  27         1       3       5000.000000     10
  28         2       4       4000.000000     20
  29         3       NULL    8000.000000     10
  30
  31 echo select * from dept;
  32 ---------------------------------------------------------
  33         dept.deptno     dept.dname      dept.lid
  34 ---------------------------------------------------------
  35         10      IT      100
  36         20      HR      300
  37
  38 echo select * from loc;
  39 ---------------------------------------------------------
  40         loc.lid loc.state
  41 ---------------------------------------------------------
  42         100     Orissa
  43         200     Cal
  44         300     Calif
  45
  46 echo select * from emp,dept,loc;
  47 ---------------------------------------------------------
  48         emp.eno emp.mgrno       emp.sal emp.deptno      dept.deptno     dept.dname      dept.lid        loc.lid loc.state
  49 ---------------------------------------------------------
  50         1       3       5000.000000     10      10      IT      100     100     Orissa
  51         1       3       5000.000000     10      10      IT      100     200     Cal
  52         1       3       5000.000000     10      10      IT      100     300     Calif
  53         1       3       5000.000000     10      20      HR      300     100     Orissa
  54         1       3       5000.000000     10      20      HR      300     200     Cal
  55         1       3       5000.000000     10      20      HR      300     300     Calif
  56         2       4       4000.000000     20      10      IT      100     100     Orissa
  57         2       4       4000.000000     20      10      IT      100     200     Cal
  58         2       4       4000.000000     20      10      IT      100     300     Calif
  59         2       4       4000.000000     20      20      HR      300     100     Orissa
  60         2       4       4000.000000     20      20      HR      300     200     Cal
  61         2       4       4000.000000     20      20      HR      300     300     Calif
  62         3       NULL    8000.000000     10      10      IT      100     100     Orissa
  63         3       NULL    8000.000000     10      10      IT      100     200     Cal
  64         3       NULL    8000.000000     10      10      IT      100     300     Calif
  65         3       NULL    8000.000000     10      20      HR      300     100     Orissa
  66         3       NULL    8000.000000     10      20      HR      300     200     Cal
  67         3       NULL    8000.000000     10      20      HR      300     300     Calif
  68
  69 echo select * from emp,dept,loc where emp.deptno!=dept.deptno AND dept.lid!=loc.lid;
  70 ---------------------------------------------------------
  71         emp.eno emp.mgrno       emp.sal emp.deptno      dept.deptno     dept.dname      dept.lid        loc.lid loc.state
  72 ---------------------------------------------------------
  73         1       3       5000.000000     10      20      HR      300     100     Orissa
  74         1       3       5000.000000     10      20      HR      300     200     Cal
  75         2       4       4000.000000     20      10      IT      100     200     Cal
  76         2       4       4000.000000     20      10      IT      100     300     Calif
  77         3       NULL    8000.000000     10      20      HR      300     100     Orissa
  78         3       NULL    8000.000000     10      20      HR      300     200     Cal
  79
  80 echo select * from emp,dept,loc where NOT(emp.deptno!=dept.deptno AND dept.lid!=loc.lid);
  81 ---------------------------------------------------------
  82         emp.eno emp.mgrno       emp.sal emp.deptno      dept.deptno     dept.dname      dept.lid        loc.lid loc.state
  83 ---------------------------------------------------------
  84         1       3       5000.000000     10      10      IT      100     100     Orissa
  85         1       3       5000.000000     10      10      IT      100     200     Cal
  86         1       3       5000.000000     10      10      IT      100     300     Calif
  87         1       3       5000.000000     10      20      HR      300     300     Calif
  88         2       4       4000.000000     20      10      IT      100     100     Orissa
  89         2       4       4000.000000     20      20      HR      300     100     Orissa
  90         2       4       4000.000000     20      20      HR      300     200     Cal
  91         2       4       4000.000000     20      20      HR      300     300     Calif
  92         3       NULL    8000.000000     10      10      IT      100     100     Orissa
  93         3       NULL    8000.000000     10      10      IT      100     200     Cal
  94         3       NULL    8000.000000     10      10      IT      100     300     Calif
  95         3       NULL    8000.000000     10      20      HR      300     300     Calif
  96
  97 echo drop table emp;
  98 Statement Executed
  99 echo drop table dept;
 100 Statement Executed
 101 echo drop table loc;
 102 Statement Executed