Simple Join

Log tables, volatile tables and meta tables can be joined.

Mach> CREATE TABLE logtable (code INT,value INT);
Created successfully.

Mach> INSERT INTO logtable VALUES(1,20 );
1 row(s) inserted.

Mach> INSERT INTO logtable VALUES(2,10 );
1 row(s) inserted.

Mach> INSERT INTO logtable VALUES(3,15 );
1 row(s) inserted.

Mach> INSERT INTO logtable VALUES(4,20 );
1 row(s) inserted.

Mach> INSERT INTO logtable VALUES(5,10 );
1 row(s) inserted.

Mach> CREATE VOLATILE table VTABLE (code INT,name VARCHAR(32));
Created successfully.

Mach> INSERT INTO vtable VALUES(1, 'Sam');
1 row(s) inserted.

Mach> INSERT INTO vtable VALUES(3, 'Thomas');
1 row(s) inserted.

Mach> INSERT INTO vtable VALUES(5, 'Micheal');
1 row(s) inserted.

Mach> INSERT INTO vtable VALUES(7, 'Jessica');
1 row(s) inserted.

Mach> SELECT name,value FROM logtable, vtable WHERE logtable.code=vtable.code;
name                              value
-------------------------------------------------
Micheal                           10
Thomas                            15
Sam                               20
[3] row(s) selected.

When JOIN is performed, you can alias a name of each join table.

SELECT c.name FROM m$sys_tables t, m$sys_columns c WHERE t.id = c.table_id AND t.name = 'T1'
AND c.id NOT IN(0, 65534) ORDER BY c.name;

c.name                                   
--------------------------------------------
ADDR
ISTYPE
SRCIP                         
[3] row(s) selected.

You can also use GROUP BY, ORDER BY and AGGREGATION FUNCTION.

Mach> SELECT t.name, COUNT(c.name) FROM m$sys_columns c, m$sys_tables t WHERE t.id = c.table_id GROUP BY t.name ORDER BY t.name;
t.name                                    count(c.name)
------------------------------------------------------------------
COMMON_TABLE                              5
DURATIONT                                 3
[2] row(s) selected.

Unlike RDBMS, the query without JOIN clause causes errors. This is because log tables have too many records so that it is difficult to expect reasonable performance. The join between more than two log tables decreases the performance. Therefore, it's better to de-normalize database schema not to cause join.

Mach> CREATE TABLE log_table1(i1 INTEGER);
Created successfully.
Mach> INSERT INTO log_table1 VALUES(1);
1 row(s) inserted.
Mach> INSERT INTO log_table1 VALUES(20);
1 row(s) inserted.
Mach> INSERT INTO log_table1 VALUES(30);
1 row(s) inserted.


Mach>CREATE TABLE log_table2(i1 INTEGER);
Created successfully.
Mach> INSERT INTO log_table2 VALUES(1);
1 row(s) inserted.
Mach> INSERT INTO log_table2 VALUES(30);
1 row(s) inserted.
Mach> INSERT INTO log_table2 VALUES(50);
1 row(s) inserted.

Mach> SELECT log_table1.i1 FROM log_table1, log_table2;
[ERR-02101 : Error in joining tables. Cannot join without join predicate.]

Mach> SELECT log_table1.i1 FROM log_table1, log_table2 where log_table1.i1 = 1;
[ERR-02101 : Error in joining tables. Cannot join without join predicate.]

Mach> SELECT log_table1.i1 from log_table1, log_table2 WHERE log_table1.i1 = log_table2.i1;
i1
--------------
30
1
[2] row(s) selected.

INNER JOIN and OUTER JOIN

ANSI-style such as INNER JOIN, LEFT OUTER JOIN, and RIGHT OUTER JOIN are supported, but not FULL OUTER JOIN.

FROM TABLE_1 [INNER|LEFT OUTER|RIGHT OUTER] JOIN TABLE_2 ON expression

In the ON clause of ANSI-style JOIN, a conditional clause is used. In outer join query, if the inner table has conditions in WHERE clause, the query will be converted into the inner join. Inner table will be filled with NULL if it doesn't satisfy ON condition.

SELECT t1.i1 t2.i1 FROM t1 LEFT OUTER JOIN t2 ON (t1.i1 = t2.i1) WHERE t2.i2 = 1;

The query above is converted to inner join due to the following t2.i2 = 1 of WHERE clause.

results matching ""

    No results matching ""