SQL Analysis & Tips
 

Feature highlight: DML-Data Manipulation Language
Credit: Imagenation,
O'Reilly's SQL in a Nutshell
 
Description:
Most developers think of SQL in terms of the DML-Data Manipulation Language: the Delete, Insert, Update and particularly the Select statements. And truly Select provides a lot of retrieval power with joins to other tables, grouping, nested subselects and other retrieval goodies. Again, it is very instructive to see how closely the major vendors have adhered to the SQL'99 standard; but there are some variations. Just enough to cause a nuisance for interoperability purposes.

Function
IBM DB2
SQLServer
MySQL 3.23.52
Oracle 9.1
PostGresSQL
Cursors
yes
yes
no
yes
yes
-Fetch into variables
yes
yes
no
yes
no
-in groups of rows
some
no
no
yes
yes
Delete
 yes
yes+hint
yes+priority
yes+options
yes+classes
-from views
yes
yes
no
yes
no
-cursor rows
yes
yes
no
partial
yes
-qualifying subquery
yes
yes
no
yes
yes
DML expressions
yes
yes
yes
yes
yes
-arithemetic operators
yes
yes+dates
yes
yes+dates
yes
-assignment to variable
yes
yes
yes
yes
yes
-Casts
yes
yes
no
no
yes
-Concatentaion
yes
yes "+"
yes concat()
yes "||"
yes "||"
Insert
yes
yes
yes+options
yes+options
yes
-into row by values
yes
yes
yes
yes
yes
-into views
yes
yes
yes
yes
yes
-by block using subquery
yes
yes
yes
yes
yes
Logical operators
yes
yes
yes
yes
yes
..all, any, some
yes
yes
yes
yes
..and, or, not
yes
yes
yes
yes
yes
..exists, in, not in
yes
yes
some
yes
some
..between, like
yes
yes
yes
yes
yes
Rollback/Commit
yes
yes
no
yes
yes
SavePoint
yes
yes
no
yes
no
Select
yes
yes
yes
yes
yes
-from+join
yes
yes
yes
only 9i for joins
yes
-group by, aggregates
yes
yes
yes
yes
yes
-having condition
yes
yes
yes
yes
yes
-order by
yes
yes
yes
yes
yes
-where filter
yes
yes
yes
yes
yes
-where subquery
yes
yes
no
yes
yes
-joins inner/2 outer/2 cross
all
all
some
some
some
-column aliases
yes
yes
yes
yes
yes
-column computes
yes
yes
yes
yes
yes
-column variables
yes
yes
yes
yes
?
-comment within -- or /* */
?
yes
yes
yes
?
-table aliases
yes
yes
yes
yes
yes
Update
yes
yes
yes
yes
yes
-views
yes
yes
no
yes
yes
-block using subquery
yes
yes
yes
yes
yes
  DB2 SQL Server MySQL Oracle 9i Postgres



 
.

 
Top of Page  Tutorials Home 
©Imagenation 2001-2004