There is also an in-depth explanation for the INNER JOIN in the “SQLite Joins” tutorial. In our example, we joined Students table with Departments table with DepartmentId column.
Then instead of using the full table’s name, we used their aliases to refer to them.We gave the Students table an alias “s” and the departments table an alias “d”.INNER JOIN Departments AS d ON s.DepartmentId = d.DepartmentId However, the same query can be written like this: SELECT s.StudentName, d.DepartmentName This query will select each student name from the “Students” table with its department name from the “Departments” table: For example, in the following query: SELECT Students.StudentName, Departments.DepartmentName This can be very useful if you are joining more than one table instead of repeating the full table name in the query, you can give each table a short alias name. This will give you all the columns in the table Students: For example, you can do this: SELECT s.* FROM Students AS s You can also give tables aliases, not just columns. The alias won’t change the column name it will just change the display name in the SELECT clause.Īlso, note that, the keyword “AS” is optional, you can put the alias name without it, something like this: SELECT StudentName 'Student Name' FROM Students Īnd it will give you the exact same output as the previous query: Note that, the column name still “ StudentName“ the column StudentName is still the same, it doesn’t change by the alias. This will give you the students’ names with the name “Student Name” instead of “StudentName” like this:
The column aliases are specified using the keyword “AS”.įor example, if you want to select the StudentName column to be returned with “Student Name” instead of “StudentName” you can give it an alias like this: SELECT StudentName AS 'Student Name' FROM Students The alias is a new name for the column that lets you select the column with a new name. It is a virtual column, created in the query for displaying the results and it won’t be created on the table. Note that, this new column Country is not actually a new column added to the table.
This will give you all the students’ columns, plus a new column “Country” like this: For example, if you want to select all the students from Students table, with a new column called a country which contains the value “USA”, you can do this: SELECT *, 'USA' AS Country FROM Students This can be handy in some situations where you have to select a constant value for all the returned rows.
To write SQL queries in an SQLite database, you have to know how the SELECT, FROM, WHERE, GROUP BY, ORDER BY, and LIMIT clauses work and how to use them.ĭuring this tutorial, you will learn how to use these clauses and how to write SQLite clauses.