SQLite doesn’t have a SHOW TABLES statement like MySQL and MariaDB have, but it does have a similar command.
In SQLite, you can use the .tables command to show a list of tables. You can alternatively use the table_list pragma to do the job.
Tag: what is
SQLite Introduces the UNIXEPOCH() Function
SQLite 3.38.0 was released on 2nd February 2022. With it came some enhancement to the date and time functions.
One of those enhancements is the addition of the UNIXEPOCH() function. This function returns a unix timestamp. That is, the number of seconds since 1970-01-01 00:00:00 UTC.
SQLite DATEADD() Equivalent
SQLite doesn’t have a DATEADD() function like SQL Server (or an ADDDATE() or ADD_DATE() function like in some other DBMSs), but it does have the DATE() function that can make additions to a date value.
SQLite also has a DATETIME() function that can make additions to a datetime value (as well as a TIME() function for time values).
VALUES Clause in SQL Server
In SQL Server, VALUES is a table value constructor that specifies a set of row value expressions to be constructed into a table.
The VALUES clause is often used with INSERT statements to insert data, but it can also be used as a derived table in either the USING clause of the MERGE statement or the FROM clause.
PostgreSQL DATEADD() Equivalent
Updated 20 April 2024 to include the date_add() function.
SQL Server has its DATEADD() function that adds an interval to a date value. MySQL’s DATE_ADD() and ADDDATE() for does the same thing, as does MariaDB’s DATE_ADD() and ADDDATE(). SQLite has a DATE() function that also provides the option of adding an interval to a given date.
Prior to version 16, PostgreSQL didn’t have a DATEADD() or equivalent function. But with PostgreSQL 16 came with the introduction of the date_add() function, which allows us to add an interval to a timestamp with time zone.
We can also add and subtract values from dates with date/time operators such as + and -.
PostgreSQL VALUES Command Explained
In PostgreSQL, the VALUES command returns a set of one or more rows as a table. It’s a table value constructor that can be used as part of a larger command, or as a standalone SQL statement.
SQLite INTERSECT Operator
In SQLite, the INTERSECT operator is used to create a compound SELECT statement that returns the intersection of the results of the left and right SELECT statements. In other words, it combines two queries, but returns only those rows that are returned in both queries.
SQLite EXCEPT Operator
In SQLite, the EXCEPT operator can be used to create a compound SELECT statement that returns the subset of rows returned by the left SELECT that are not returned by the right SELECT.
SQLite UNION Operator
In SQLite, the UNION operator creates a compound SELECT statement that returns the results of the left and right SELECT statements. In other words, it combines the results from two queries into a single result set.
MySQL TABLE Statement
In MySQL, the TABLE statement returns rows and columns of the given table.
The TABLE statement is similar to the SELECT statement, and it can be used as a kind of shorthand version of the SELECT statement.
The TABLE statement was introduced in MySQL 8.0.19.