SQL Dot Notation

What do the dots mean in this SQL query?

You've asked several questions here. To address the dots:

In the FROM clause, a is used as an alias for the invheader table. This means you can reference that table by the short alias a instead of the full table name.

Therefore, a.id refers the the id column of the invheader table.

It is generally considered bad practice to simply give your tables the aliases a, b, c, etc. and I would recommend you use something more useful.

I suggest you read some basic MySQL tutorials as this is a fundamental principal.

Dot Notation in SQL FROM clause

Here, customer refers to database name and user refers to table name.Usually we follow FUlly qualified name for tables as database_name.table_name

Is there any term like 'DOT(.) notation' used in SQL joins?

Yes here is how you do it

When you do your SELECT

SELECT firstname, lastname from dbo.names n -- The n becomes an alias
JOIN address a --- another alias

on a.userid = n.userid

Is it good practice to use dots within table names in MySQL

In MySQL? No, I would think that it's not good practice to use periods in table names at all. I would think that it's very bad practice. The dot is the reference operator in SQL. That means if you want to refer to a column using fully qualified notation, you do so like this:

SELECT Table.Column_A ...

Or, with backtick quoting:

SELECT `Table`.`Column_A` ...

Now, imagine if your table is named StructureX.Table. Just like with a space, you've got to quote that to escape it because you don't want MySQL to think the dot is an operator. That means your SQL has to look like this:

SELECT `StructureX.Table`.Column_A ...

Or, with backtick quoting:

SELECT `StructureX.Table`.`Column_A` ...

Doesn't that look like a syntax error to you? Like maybe it's supposed to be like this:

SELECT `StructureX`.`Table`.`Column_A` ...

This would be a nightmare to maintain and as a systems analyst I would hate any application or developer that inflicted this nomenclature on me. It makes me want to claw my eyes out.

Microsoft SQL Server is different because it supports multiple schemas within a single database, while MySQL treats schema as a synonym for database. In MS SQL Server, schemas are collections of objects, and you can use them to organize your tables, or apply security to tables as a group. The default schema is dbo, which is why you see that one listed so often. In MS SQL Server syntax, this:

SELECT [StructureX].[Table].[Column_A] ...

Means within the current database, the schema named StructureX, table named Table, and column name Column_A. MS SQL Server actually supports a four part name, with the fourth part being the database:

SELECT [MyDatabase].[StructureX].[Table].[Column_A] ...

Here, MyDatabase is the database name.

That same style works in MySQL, except you have to remember that schema and database are synonymous. So there, this:

SELECT `StructureX`.`Table`.`Column_A` ...

Would mean database StructureX, table Table, and column Column_A.

---

Related Topics