On 2018/06/26 4:42 PM, Csányi Pál wrote:
Then I get help and this code:
INSERT INTO SchoolYearTeachingDays
SELECT aDate FROM TeachingSaturdaysInSchoolYear T WHERE T.aDate NOT
IN (SELECT S.aDate FROM SchoolYearTeachingDays S)
This says "insert all dates that aren't already present", which of course
avoids UNIQUE constraint.
Just do not understand what are the 'T' and 'S' means out there, after
FROM clause.
Are they CTEs? Are they virtual tables?
The T and S are substitute identifiers that represent the tables
"SchoolYearTeachingDays" and "TeachingSaturdaysInSchoolYear". These
substitute names are called "Aliases" which is something like a
short-name or nick-name for something with a long name which we do not
want to type every time. Much like in school when someone was named
"Josephine" but everyone just called her "Jo" for short, then "Jo" is an
Alias for "Josephine", like in the above query where "S" is an alias for
"SchoolYearTeachingDays".
The main reason we use aliases, which other posts have highlighted
already, is simply to spare ourselves the effort of having to type it
out many times, but it's also handy for avoiding confusion when we have
two references to the same table, or two tables with column names that
are the same.
In the query above, the reason we need to name the table is so that we
make sure the "aDate" in the SELECT refers to the aDate from the
"SchoolYearTeachingDays" table in the correlated sub-query (the bit
inside parentheses) and not the outside query, which refers to table
"TeachingSaturdaysInSchoolYear", which may also have an "aDate" column.
So to avoid ambiguity or confusion about which aDate we are talking
about, we must specify the table it is in. But we don't want to write
the full table name every time, so in stead we use the aliases.
One way to tell SQL we intend to use an alias is to use an "AS" clause,
so we could say:
SELECT A.id FROM MyLongNamedTable AS A WHERE A.id > 5
Here we say we will get data from "MyLongNamedTable" but we will use it
AS if it was named "A" so that everywhere else we will be able to refer
to A.this or A.that in stead of having to type MyLongNamedTable.this and
MyLongNamedTable.that.
We can also do this for column names in the return header, so if I want
to get the id from MyTable but we want it to be more descriptive, we
could do:
SELECT id AS MyTableID FROM MyTable...
This will output a list of IDs under the aliased heading: "MyTableID".
Lastly, as Simon mentioned, the SQL standard allows us to drop the word
"AS" as long as we specify the known identifier directly followed by its
Alias, so the previous query can also be written like:
SELECT id MyTableID FROM MyTable...
There is a school of thought that prefers this "id MyTableID" aliasing
due to its brevity, and another that prefers the full "id AS MyTableID"
due to its clarity.
I don't think there is a "Right" way. Personally I use the first in my
actual queries, but the second when using a query in an explanation.
Hope that makes it clear.
PS: Apologies for the overly "wordy" response, but it seems to me the OP
is not natively English, or might read this through a translator, and I
find for those to make sense in a technical translation, it is best if
they have a lot of data to work with, saying the same thing in different
ways, etc.
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
