Duckett George A. - SQL Programming: Questions and Answers

Wiki by user john-saunders

SQL stands for Structured Query Language.

One subset of the SQL standard is DDL (Data Definition Language), which is used to create tables and constraints. These include:

• CREATE
• DROP
• ALTER

Another subset is DML (Data Manipulation Language), which is used to modify and view data within the database:

• SELECT
• INSERT
• UPDATE
• DELETE

The final "standard" subset of commands is DCL (Data Control Language):

• GRANT
• REVOKE

Many database implementations require the use of SQL, and over the years, vendors have implemented dialects of SQL to provide more functionality as well as simplify it. Because of these deviations from the standard, SQL is fractured - syntax that works on one implementations does not necessarily work on another.

ISO/IEC (formerly ANSI) standards have been beneficial in resolving such situations, but adoption is selective. Queries conforming to these standards should be portable to other databases, though performance may vary.

Most DBMSs have additional languages for writing stored procedures. In Oracle this is PL/SQL (Procedural Language/Structured Query Language), in PostgreSQL it's PL/pgSQL (Procedural Language/PostgreSQL). Outside of stored procedures or functions, Oracle and PostgreSQL use SQL. Thus the tags ) for both "plain" SQL (queries, DML, ..) and the language used for stored procedures.

This tag should be used for general SQL programming language questions, in addition to tags for specific products. For example, questions about Microsoft SQL Server should use the questions to omit this tag because query discussions on MySQL are more often stated as MySQL rather than SQL in general.

Please read this summary about the SQL standard (the 1992 one in this case, broadly implemented) and if you can, refer to the book itself.

• Developing Time-Oriented Database Applications in SQL
• Use The Index, Luke!: A Guide To SQL Database Performance
• Learn SQL The Hard Way
• SQL Tutorial For Starters
• SQL - Free books
• SQL - Free books 2

• Coursera-Introduction to Databases
• Stanford Online-DB: Introduction to Databases

• Codeschool Try SQL

While you should always provide complete code examples (e.g. schema, data sample and expected result) in your question or answer, you can also isolate problematic code and reproduce it in an online environment such as SQL Fiddle, and link to that.

MySQL is managed and maintained by Oracle and in-depth documentation can be found at the MySQL website.

When you are asking a question about SQL you can also add more specific tags. Here is the list of available tags:

• sqlbulkcopy
• sqlconnection
• sqlcommand
• sql-copy
• sql-convert
• sql-delete
• sql-drop
• sqldatetime
• sql-date-functions
• sql-function
• sql-job
• sql-like
• sql-limit
• sql-merge
• sql-returning
• sql-server-job
• to-date
• sql-timestamp
• sql-update
• sql-view
• stored-procedures
• having
• where-clause
• count
• left-join
• inner-join
• outer-join
• self-join
• cross-join
• right-join
• full-outer-join
• natural-join

You can specify your question by adding the implementation you used as a tag.

• mysqli
• db2

I have the following query:

What would be the difference if I replaced all calls to count(column_name) to count(*)?

This question was inspired by .

To clarify the accepted answer (and maybe my question), replacing count(column_name) with count(*) would return an extra row in the result that contains a null and the count of null values in the column.

I've seen a number of people claim that you should specifically name each column you want in your select query.

Assuming I'm going to use all of the columns anyway, why would I not use SELECT *?

Even considering the question *SQL query - Select * from view or Select col1, col2, colN from view*, I don't think this is an exact duplicate as I'm approaching the issue from a slightly different perspective.

One of our principles is to not optimize before it's time. With that in mind, it seems like using SELECT * should be the preferred method until it is proven to be a resource issue or the schema is pretty much set in stone. Which, as we know, won't occur until development is completely done.

That said, is there an overriding issue to not use SELECT *?