I’ve started to use a SQLite View that is an INNER JOIN. Seems a lot easier than writing out the logic of that particular INNER JOIN every time I need it, which is a number of times in this particular project.
If I end up moving from SQLite to another SQL database, can I safely assume that other database will honor such a view? Or does view logic change from db to db? This latter consideration is why I don’t use Triggers in SQLite.
Are there other considerations I should be aware of that might make me not use a SQLite View?
Alternatively, I guess I can always just write a function that returns that INNER JOIN and use that in place of a View.
Views logic is going to be the same between different SQL DBs. but you might need to update the SQL a little when switching DB engines.
I use VIEWs a lot when I need a particular subset of the data (especially when the data is spread across multiple tables). Or if I want to expose only a subset of the data to the end user.
Ive not encountered another database that doesn’t honor the INNER JOIN syntax from the SQL 89 or 92 standards.
INNER JOIN is effectively the default anyway…
select name,car from people,carsowned where people.personid = carsowned.personid;
is the same as
select name,car from people INNER JOIN carsowned on people.personid = carsowned.personid;
Its usually the outer joins where things go ‘specific’
For example Oracle’s use of (+) as an outer join shorthand is obscure (and tbh I do not know many people who use it)
select name,car from people , carsowned where people.personid (+)= carsowned.personid;
…left outer join so you get all people even if they have no cars…