#top Przydatne informacje¶

#top Timeout¶

#top Database Storage Layout¶

#top Tablespace¶

#top SQL JOIN Visual Explanation¶

• [INNER] JOIN - słowo kluczowe INNER jest opcjonalne, złączenia JOIN i INNER JOIN są równoważne,
• LEFT [OUTER] JOIN - słowo kluczowe OUTER jest opcjonalne, złączenia LEFT JOIN i LET OUTER JOIN są równoważne,
• RIGHT [OUTER] JOIN - słowo kluczowe OUTER jest opcjonalne, złączenia RIGHT JOIN i RIGHT OUTER JOIN są równoważne,
• FULL [OUTER] JOIN - słowo kluczowe OUTER jest opcjonalne, złączenia FULL JOIN i FULL OUTER JOIN są równoważne,
• CROSS JOIN

-- utworzenie tabel
CREATE TABLE join01 (
idjoin integer NOT NULL,
name varchar(255) NOT NULL,
PRIMARY KEY(idjoin)
);

CREATE TABLE join02 (
idjoin integer NOT NULL,
name varchar(255) NOT NULL,
PRIMARY KEY(idjoin)
);

-- dodanie przykładowych danych
INSERT INTO join01(idjoin,name) VALUES(1,'t01name01');
INSERT INTO join01(idjoin,name) VALUES(2,'t01name02');
INSERT INTO join01(idjoin,name) VALUES(3,'t01name03');
INSERT INTO join01(idjoin,name) VALUES(4,'t01name04');
INSERT INTO join01(idjoin,name) VALUES(5,'t01name05');

INSERT INTO join02(idjoin,name) VALUES(3,'t02name03');
INSERT INTO join02(idjoin,name) VALUES(4,'t02name04');
INSERT INTO join02(idjoin,name) VALUES(5,'t02name05');
INSERT INTO join02(idjoin,name) VALUES(6,'t02name06');
INSERT INTO join02(idjoin,name) VALUES(7,'t02name07');

#top SQL JOIN INNER JOIN¶

SELECT * FROM join01 JOIN join02 ON (join01.idjoin=join02.idjoin);
SELECT * FROM join01 INNER JOIN join02 ON (join01.idjoin=join02.idjoin);

|idjoin|name     |idjoin|name     |
+------+---------+------+---------+
|3     |t01name03|3     |t02name03|
|4     |t01name04|4     |t02name04|
|5     |t01name05|5     |t02name05|
Retrieved 3 rows in 0.006 seconds.

#top SQL JOIN LEFT JOIN¶

SELECT * FROM join01 LEFT JOIN join02 ON (join01.idjoin=join02.idjoin);
SELECT * FROM join01 LEFT OUTER JOIN join02 ON (join01.idjoin=join02.idjoin);

|idjoin|name     |idjoin|name     |
+------+---------+------+---------+
|1     |t01name01|      |         |
|2     |t01name02|      |         |
|3     |t01name03|3     |t02name03|
|4     |t01name04|4     |t02name04|
|5     |t01name05|5     |t02name05|
Retrieved 5 rows in 0.005 seconds.

#top SQL JOIN RIGHT JOIN¶

SELECT * FROM join01 RIGHT JOIN join02 ON (join01.idjoin=join02.idjoin);
SELECT * FROM join01 RIGHT OUTER JOIN join02 ON (join01.idjoin=join02.idjoin);

|idjoin|name     |idjoin|name     |
+------+---------+------+---------+
|3     |t01name03|3     |t02name03|
|4     |t01name04|4     |t02name04|
|5     |t01name05|5     |t02name05|
|      |         |6     |t02name06|
|      |         |7     |t02name07|
Retrieved 5 rows in 0.006 seconds.

#top SQL JOIN FULL JOIN¶

SELECT * FROM join01 FULL JOIN join02 ON (join01.idjoin=join02.idjoin);
SELECT * FROM join01 FULL OUTER JOIN join02 ON (join01.idjoin=join02.idjoin);

|idjoin|name     |idjoin|name     |
+------+---------+------+---------+
|1     |t01name01|      |         |
|2     |t01name02|      |         |
|3     |t01name03|3     |t02name03|
|4     |t01name04|4     |t02name04|
|5     |t01name05|5     |t02name05|
|      |         |6     |t02name06|
|      |         |7     |t02name07|
Retrieved 7 rows in 0.007 seconds.

#top SQL JOIN LEFT JOIN IS NULL¶

SELECT * FROM join01 LEFT JOIN join02 ON (join01.idjoin=join02.idjoin) WHERE join02.idjoin IS NULL;
SELECT * FROM join01 LEFT OUTER JOIN join02 ON (join01.idjoin=join02.idjoin) WHERE join02.idjoin IS NULL;

|idjoin|name     |idjoin|name|
+------+---------+------+----+
|1     |t01name01|      |    |
|2     |t01name02|      |    |
Retrieved 2 rows in 0.182 seconds.

#top SQL JOIN RIGHT JOIN IS NULL¶

SELECT * FROM join01 RIGHT JOIN join02 ON (join01.idjoin=join02.idjoin) WHERE join01.idjoin IS NULL;
SELECT * FROM join01 RIGHT OUTER JOIN join02 ON (join01.idjoin=join02.idjoin) WHERE join01.idjoin IS NULL;

|idjoin|name|idjoin|name     |
+------+----+------+---------+
|      |    |6     |t02name06|
|      |    |7     |t02name07|
Retrieved 2 rows in 0.056 seconds.

#top SQL JOIN FULL JOIN IS NULL¶

SELECT * FROM join01 FULL JOIN join02 ON (join01.idjoin=join02.idjoin) WHERE join01.idjoin IS NULL OR join02.idjoin IS NULL;
SELECT * FROM join01 FULL OUTER JOIN join02 ON (join01.idjoin=join02.idjoin) WHERE join01.idjoin IS NULL OR join02.idjoin IS NULL;

|idjoin|name     |idjoin|name     |
+------+---------+------+---------+
|1     |t01name01|      |         |
|2     |t01name02|      |         |
|      |         |6     |t02name06|
|      |         |7     |t02name07|
Retrieved 4 rows in 0.128 seconds.

#top SQL JOIN CROSS JOIN¶

SELECT * FROM join01 CROSS JOIN join02;

|idjoin|name      |idjoin|name     |
+------+----------+------+---------+
|1     |t01name01 |3     |t02name03|
|2     |t01name02 |3     |t02name03|
|3     |t01name03 |3     |t02name03|
|4     |t01name04 |3     |t02name03|
|5     |t01name05 |3     |t02name03|
|1     |t01name01 |4     |t02name04|
|2     |t01name02 |4     |t02name04|
|3     |t01name03 |4     |t02name04|
|4     |t01name04 |4     |t02name04|
|5     |t01name05 |4     |t02name04|
|1     |t01name01 |5     |t02name05|
|2     |t01name02 |5     |t02name05|
|3     |t01name03 |5     |t02name05|
|4     |t01name04 |5     |t02name05|
|5     |t01name05 |5     |t02name05|
|1     |t01name01 |6     |t02name06|
|2     |t01name02 |6     |t02name06|
|3     |t01name03 |6     |t02name06|
|4     |t01name04 |6     |t02name06|
|5     |t01name05 |6     |t02name06|
|1     |t01name01 |7     |t02name07|
|2     |t01name02 |7     |t02name07|
|3     |t01name03 |7     |t02name07|
|4     |t01name04 |7     |t02name07|
|5     |t01name05 |7     |t02name07|
Retrieved 25 rows in 0.007 seconds.

#top Database Objects Size¶

#top Find Multiple Indexes¶

#top Find Duplicate Indexes¶

#top Find Unused Indexes¶

#top Performance Tuning¶