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

Question

Is there any term like 'DOT(.) notation' used in SQL joins? if practised, pls explain how to use it.

Thanks in advance.

Answer 1

Collated from multiple sources of official documentation.

Dot notation (sometimes called the membership operator) allows you to qualify an SQL identifier with another SQL identifier of which it is a component. You separate the identifiers with the period ( . ) symbol. For example, you can qualify a column name with any of the following SQL identifiers:

Table name: table_name.column_name
View name: view_name.column_name
Synonym name: syn_name.column_name

These forms of dot notation are called column projections.

You can also use dot notation to directly access the fields of a named or unnamed ROW column, as in the following example:

row-column name.field name

This use of dot notation is called a field projection. For example, suppose you have a column called rect with the following definition:

CREATE TABLE rectangles
(
   area float,
   rect ROW(x int, y int, length float, width float)
)

The following SELECT statement uses dot notation to access field length of the rect column:

SELECT rect.length FROM rectangles WHERE area = 64

Selecting Nested Fields

When the ROW type that defines a column itself contains other ROW types, the column contains nested fields. Use dot notation to access these nested fields within a column.

For example, assume that the address column of the employee table contains the fields: street, city, state, and zip. In addition, the zip field contains the nested fields: z_code and z_suffix. A query on the zip field returns values for the z_code and z_suffix fields. You can specify, however, that a query returns only specific nested fields. The following example shows how to use dot notation to construct a SELECT statement that returns rows for the z_code field of the address column only:

SELECT address.zip.z_code 
   FROM employee 

Rules of Precedence

The database server uses the following precedence rules to interpret dot notation:

schema name_a . table name_b . column name_c . field name_d
table name_a . column name_b . field name_c . field name_d
column name_a . field name_b . field name_c . field name_d

When the meaning of an identifier is ambiguous, the database server uses precedence rules to determine which database object the identifier specifies. Consider the following two tables:

CREATE TABLE b (c ROW(d INTEGER, e CHAR(2));
CREATE TABLE c (d INTEGER);

In the following SELECT statement, the expression c.d references column d of table c (rather than field d of column c in table b) because a table identifier has a higher precedence than a column identifier:

SELECT * 
FROM b,c 
WHERE c.d = 10

For more information about precedence rules and how to use dot notation with ROW columns, see the IBM Informix: Guide to SQL Tutorial.

Using Dot Notation with Row-Type Expressions

Besides specifying a column of a ROW data type, you can also use dot notation with any expression that evaluates to a ROW type. In an INSERT statement, for example, you can use dot notation in a subquery that returns a single row of values. Assume that you created a ROW type named row_t:

CREATE ROW TYPE row_t (part_id INT, amt INT)

Also assume that you created a typed table named tab1 that is based on the row_t ROW type:

CREATE TABLE tab1 OF TYPE row_t

Assume also that you inserted the following values into table tab1:

INSERT INTO tab1 VALUES (ROW(1,7));
INSERT INTO tab1 VALUES (ROW(2,10));

Finally, assume that you created another table named tab2:

CREATE TABLE tab2 (colx INT)

Now you can use dot notation to insert the value from only the part_id column of table tab1 into the tab2 table:

INSERT INTO tab2
   VALUES ((SELECT t FROM tab1 t
      WHERE part_id = 1).part_id)

The asterisk form of dot notation is not necessary when you want to select all fields of a ROW-type column because you can specify the column name alone to select all of its fields. The asterisk form of dot notation can be quite helpful, however, when you use a subquery, as in the preceding example, or when you call a user-defined function to return ROW-type values.

Suppose that a user-defined function named new_row returns ROW-type values, and you want to call this function to insert the ROW-type values into a table. Asterisk notation makes it easy to specify that all the ROW-type values that the new_row( ) function returns are to be inserted into the table:

INSERT INTO mytab2 SELECT new_row (mycol).* FROM mytab1

References to the fields of a ROW-type column or a ROW-type expression are not allowed in fragment expressions. A fragment expression is an expression that defines a table fragment or an index fragment in SQL statements like CREATE TABLE, CREATE INDEX, and ALTER FRAGMENT.

Additional Examples of How to Specify Names With the Dot Notation Dot notation is used for identifying record fields, object attributes, and items inside packages or other schemas. When you combine these items, you might need to use expressions with multiple levels of dots, where it is not always clear what each dot refers to. Here are some of the combinations:

Field or Attribute of a Function Return Value

func_name().field_name
func_name().attribute_name
Schema Object Owned by Another Schema

schema_name.table_name
schema_name.procedure_name()
schema_name.type_name.member_name()
Packaged Object Owned by Another User

schema_name.package_name.procedure_name()
schema_name.package_name.record_name.field_name
Record Containing an Object Type

record_name.field_name.attribute_name
record_name.field_name.member_name()

Differences in Name Resolution Between PL/SQL and SQL The name resolution rules for PL/SQL and SQL are similar. You can avoid the few differences if you follow the capture avoidance rules. For compatibility, the SQL rules are more permissive than the PL/SQL rules. SQL rules, which are mostly context sensitive, recognize as legal more situations and DML statements than the PL/SQL rules.

PL/SQL uses the same name-resolution rules as SQL when the PL/SQL compiler processes a SQL statement, such as a DML statement. For example, for a name such as HR.JOBS, SQL matches objects in the HR schema first, then packages, types, tables, and views in the current schema.

PL/SQL uses a different order to resolve names in PL/SQL statements such as assignments and procedure calls. In the case of a name HR.JOBS, PL/SQL searches first for packages, types, tables, and views named HR in the current schema, then for objects in the HR schema.

Answer 2

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