#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.002 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.002 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.002 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)

ERROR: 0:The column prefix 'join01' does not match with a table name or alias name used in the query. Either the table is not specified in the FROM clause or it has a correlation name which must be used instead.
ERROR: 0:Incorrect syntax near 'OUTER'.

#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.265 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.002 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

ERROR: 0:The column prefix 'join01' does not match with a table name or alias name used in the query. Either the table is not specified in the FROM clause or it has a correlation name which must be used instead.
ERROR: 0:Incorrect syntax near 'OUTER'.

#top SQL JOIN CROSS JOIN¶

SELECT * FROM join01 CROSS JOIN join02

ERROR: 0:Incorrect syntax near 'join02'.

SELECT * FROM join01, join02

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

#top Database Objects Size¶

#top Find Multiple Indexes¶

#top Find Duplicate Indexes¶

#top Find Unused Indexes¶

#top Performance Tuning¶