using sql standards? database sql comparition
DESCRIPTION
Interesting to learn about different databases. Do they actually follow SQL standards ? - Oracle- MySQL- PostgreSQLTRANSCRIPT
![Page 1: Using SQL Standards? Database SQL comparition](https://reader033.vdocuments.us/reader033/viewer/2022042613/554a155eb4c90507558b50bb/html5/thumbnails/1.jpg)
SQL COMPARISONDenish Patel
Database AdministratorOmniTi Computer Consulting Inc
Using SQL Standards
Friday, April 2, 2010
![Page 2: Using SQL Standards? Database SQL comparition](https://reader033.vdocuments.us/reader033/viewer/2022042613/554a155eb4c90507558b50bb/html5/thumbnails/2.jpg)
SQL STANDARDS
SQL92
SQL99
SQL 2003
SQL 2008
Friday, April 2, 2010
![Page 3: Using SQL Standards? Database SQL comparition](https://reader033.vdocuments.us/reader033/viewer/2022042613/554a155eb4c90507558b50bb/html5/thumbnails/3.jpg)
DATABASES
Oracle 10g Express Edition
PostgreSQL 8.3.3
MySQL 5.0.18
Friday, April 2, 2010
![Page 4: Using SQL Standards? Database SQL comparition](https://reader033.vdocuments.us/reader033/viewer/2022042613/554a155eb4c90507558b50bb/html5/thumbnails/4.jpg)
VIEWS
SQL92:Updatable views can not be derived from more than one base table
SQL99: As long as it “make sense”
SQL:2008: As long as the update-operation translates into unambiguous change
Friday, April 2, 2010
![Page 5: Using SQL Standards? Database SQL comparition](https://reader033.vdocuments.us/reader033/viewer/2022042613/554a155eb4c90507558b50bb/html5/thumbnails/5.jpg)
VIEWS
Oracle Conforms at least SQL92
PostgreSQLBreak rules by not allowing
updatable views but offer non-standard ‘rule’ system
MySQL Conforms at least SQL92
Friday, April 2, 2010
![Page 6: Using SQL Standards? Database SQL comparition](https://reader033.vdocuments.us/reader033/viewer/2022042613/554a155eb4c90507558b50bb/html5/thumbnails/6.jpg)
JOIN TYPES
Oracle PostgreSQL MySQL
Natural Join Yes Yes Yes
Using-clause Yes Yes Yes
Full Join Yes Yes No
Explicit(Cross Join)
Yes Yes Yes
Friday, April 2, 2010
![Page 7: Using SQL Standards? Database SQL comparition](https://reader033.vdocuments.us/reader033/viewer/2022042613/554a155eb4c90507558b50bb/html5/thumbnails/7.jpg)
ORDER BY
SQL Standard : Relations are unordered but result sets may be ordered when returned to the user using cursor
Doesn’t specify NULLs order
NULLs are equally ordered and can be first or last
...ORDER BY...NULLS FIRST
...ORDER BY ...NULLS LAST
DECLARE cursorname CURSOR FOR
SELECT ...FROM ....WHERE...
ORDER BY column_name1,column_name2,....
Friday, April 2, 2010
![Page 8: Using SQL Standards? Database SQL comparition](https://reader033.vdocuments.us/reader033/viewer/2022042613/554a155eb4c90507558b50bb/html5/thumbnails/8.jpg)
ORDER BY
Oracle- Cursor allows ORDER BY
- By Default NULLs considered higher- ORDER BY ... NULLS LAST
PostgreSQL- Cursor allows ORDER BY
- By Default NULLs considered higher- ORDER BY ... NULLS LAST
MySQL
Cursor allows ORDER BY- By Default NULLs considered lower
- minus(-) character used to change from ASC to DESC or Vice-Versa
Friday, April 2, 2010
![Page 9: Using SQL Standards? Database SQL comparition](https://reader033.vdocuments.us/reader033/viewer/2022042613/554a155eb4c90507558b50bb/html5/thumbnails/9.jpg)
SIMPLE LIMIT
FETCH FIRST (Since SQL:2008)
Window Function(Since SQL:2003)
Cursor
SELECT ...FROM ..WHERE...ORDER BY...FETCH FIRST n ROW(s) ONLY
SELECT * FROM
( SELECT ROW_NUMBER() OVER (ORDER BY key ASC) as rownumber, columns
FROM tablename ) as foo
WHERE rownumber <=n
DECLARE , OPEN , FETCH , CLOSE
Friday, April 2, 2010
![Page 10: Using SQL Standards? Database SQL comparition](https://reader033.vdocuments.us/reader033/viewer/2022042613/554a155eb4c90507558b50bb/html5/thumbnails/10.jpg)
SIMPLE LIMIT
Oracle-Supports ROW_NUMBER but not FEATCH
FIRST
PostgreSQL
- Doesn’t support ROW_NUMBER() or FETCH FIRST
- Alternative LIMIT(*SELECT ..FOR UPDATE)
MySQL
- Doesn’t support ROW_NUMBER() or FETCH FIRST
- Alternative LIMIT
Friday, April 2, 2010
![Page 11: Using SQL Standards? Database SQL comparition](https://reader033.vdocuments.us/reader033/viewer/2022042613/554a155eb4c90507558b50bb/html5/thumbnails/11.jpg)
MULTI- INSERT
OracleNot Supported
insert into tablename values (0,‘foo’);insert into tablename values (1,‘bar’);
PostgreSQLSupported
insert into tablename values (0,‘foo’), (1,‘bar’);
MySQLSupported
insert into tablename values (0,‘foo’), (1,‘bar’);
Friday, April 2, 2010
![Page 12: Using SQL Standards? Database SQL comparition](https://reader033.vdocuments.us/reader033/viewer/2022042613/554a155eb4c90507558b50bb/html5/thumbnails/12.jpg)
TYPES- BOOLEAN
BOOLEAN type is Optional!!
TRUE, FALSE, UNKNOWN or NULL
UNKNOWN can be NULL!
TRUE > FALSE
Friday, April 2, 2010
![Page 13: Using SQL Standards? Database SQL comparition](https://reader033.vdocuments.us/reader033/viewer/2022042613/554a155eb4c90507558b50bb/html5/thumbnails/13.jpg)
TYPES- BOOLEAN
OracleNot Supported
Alternative NUMBER(1)Do not forget to add constraint
PostgreSQLFollow Standards
Does not accept UNKNOWN
MySQLNonconforming alternative TINYINT(1)
Accept values greater than 1
Friday, April 2, 2010
![Page 14: Using SQL Standards? Database SQL comparition](https://reader033.vdocuments.us/reader033/viewer/2022042613/554a155eb4c90507558b50bb/html5/thumbnails/14.jpg)
TYPES- CHAR
CHAR-length
Return error if the inserted sting is too long except spaces
Pad with spaces if string is shorter
Casting or Compare (VARCHAR) - Pad with trailing spaces
Friday, April 2, 2010
![Page 15: Using SQL Standards? Database SQL comparition](https://reader033.vdocuments.us/reader033/viewer/2022042613/554a155eb4c90507558b50bb/html5/thumbnails/15.jpg)
TYPES- CHAR
OracleGenerally Follows Standard
Doesn’t remove trailing spaces when exceeds CHAR length
PostgreSQLGenerally Follows Standard
Truncate trailing spaces i.e CHARACTER_LENGTH
MySQLBreaks the rules
Silently inserting truncated stringTruncates all trailing spaces
Friday, April 2, 2010
![Page 16: Using SQL Standards? Database SQL comparition](https://reader033.vdocuments.us/reader033/viewer/2022042613/554a155eb4c90507558b50bb/html5/thumbnails/16.jpg)
TYPES- TIMESTAMP
second - 6 fractional digits
TIMESTAMP ‘2009-03-05 16:10:18.299411’
TIMESTAMP WITH TIME ZONE
TIMESTAMP ‘2009-03-05 16:10:18.299411+05:00’
Friday, April 2, 2010
![Page 17: Using SQL Standards? Database SQL comparition](https://reader033.vdocuments.us/reader033/viewer/2022042613/554a155eb4c90507558b50bb/html5/thumbnails/17.jpg)
TYPES- TIMESTAMP
Oracle Follows standard
PostgreSQLFollows Standards with exceptionTIMESTAMP & TIMESTAMPTZIllogical Standard is Standard!
MySQL
Not SupportedMagical TIMESTAMP datatype
No Sanity checks on the date ‘2009-03-32 00:00:00’
Friday, April 2, 2010
![Page 18: Using SQL Standards? Database SQL comparition](https://reader033.vdocuments.us/reader033/viewer/2022042613/554a155eb4c90507558b50bb/html5/thumbnails/18.jpg)
UNIQUE CONSTRAINT
Unique Values or NULL
NULL <> NULL
Nulls allowed feature
create table test(a number,b number, unique (a,b));
insert into test values (1,NULL);
insert into test values(1,NULL);
Friday, April 2, 2010
![Page 19: Using SQL Standards? Database SQL comparition](https://reader033.vdocuments.us/reader033/viewer/2022042613/554a155eb4c90507558b50bb/html5/thumbnails/19.jpg)
UNIQUE CONSTRAINT
Oracle Follows standard without Nulls allowed
PostgreSQL Follows standard with Nulls Allowed
MySQL Follows standard with Nulls Allowed
Friday, April 2, 2010
![Page 20: Using SQL Standards? Database SQL comparition](https://reader033.vdocuments.us/reader033/viewer/2022042613/554a155eb4c90507558b50bb/html5/thumbnails/20.jpg)
AUTO KEY
GENERATED .. [ALWAYS /BY DEFAULT] AS IDENTITY
ALWAYS - user can not specify value
BY DEFAULT - user can manually specify value
Friday, April 2, 2010
![Page 21: Using SQL Standards? Database SQL comparition](https://reader033.vdocuments.us/reader033/viewer/2022042613/554a155eb4c90507558b50bb/html5/thumbnails/21.jpg)
AUTO KEY
OracleDoes not support IDENTIFY attribute
Sequence & TriggerException to implement ALWAYS
PostgreSQLDoes not support IDENTIFY attribute
In-line SERIAL attribute Trigger to implement ALWAYS
MySQL
Does not support IDENTIFY attributeAUTO INCREMENT
Never fails (!)
Friday, April 2, 2010
![Page 22: Using SQL Standards? Database SQL comparition](https://reader033.vdocuments.us/reader033/viewer/2022042613/554a155eb4c90507558b50bb/html5/thumbnails/22.jpg)
TRUNCATE TABLE
Delete all rows without causing triggered action
Need more clarification on
TRUNCATE TABLE should be allowed in transaction
Immediate Commit
Friday, April 2, 2010
![Page 23: Using SQL Standards? Database SQL comparition](https://reader033.vdocuments.us/reader033/viewer/2022042613/554a155eb4c90507558b50bb/html5/thumbnails/23.jpg)
TRUNCATE TABLE
OracleFollows the standard
Implicit commit
PostgreSQLFollows the standard
Allowed with other transaction Does not implicit commit
MySQL
Doesn’t ALWAYS follow standardSlow and Trigger Invoking
For Innodb, Allowed with other transaction & does implicit commit
Friday, April 2, 2010
![Page 24: Using SQL Standards? Database SQL comparition](https://reader033.vdocuments.us/reader033/viewer/2022042613/554a155eb4c90507558b50bb/html5/thumbnails/24.jpg)
MISC
CL- interfaceDefault port
DB NameVersion Name
List Schema List Tables
Oracle sqlplus1521
select name from v$database;select * from v$version;
select username from all_users;select * from tab;
PostgreSQL psql5432
\l or psql --listselect version();
\dn or select schemaname from information_schema.schemata;
\dt or select tablename from pg_tables;
MySQL mysql3306
show databases;select version();
Does not support schemashow tables;
Friday, April 2, 2010
![Page 25: Using SQL Standards? Database SQL comparition](https://reader033.vdocuments.us/reader033/viewer/2022042613/554a155eb4c90507558b50bb/html5/thumbnails/25.jpg)
RESOURCES
SQL Standards Documentations/Books:
SQL99: http://www.elsevierdirect.com/product.jsp?isbn=9781558606777
SQL2008: http://www.wiscorp.com/sql200n.zip (File 5CD2-02-Foundation-2006-01.pdf)
http://troels.arvin.dk/db/rdbms/#cli
Oracle Documentation: http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/toc.htm
PostgreSQL Documentation: http://www.postgresql.org/docs/8.3/static/sql-commands.html
MySQL documentaion: http://dev.mysql.com/doc/refman/5.0/en/
Friday, April 2, 2010
![Page 26: Using SQL Standards? Database SQL comparition](https://reader033.vdocuments.us/reader033/viewer/2022042613/554a155eb4c90507558b50bb/html5/thumbnails/26.jpg)
THANK YOU!!
Friday, April 2, 2010