Once again, thank you all very much for your help. I understand now this query.
R Smith <ryansmit...@gmail.com> ezt írta (időpont: 2018. jún. 26., K, 17:28): > > > 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 -- Best, Pali _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
