Skip to content

Tag: sql

Why to use Correlated Subqueries?

As far as I’ve seen, correlated subqueries can be re-written using multiple-column subqueries or joins. And they usually perform better than correlated subqueries. So in which possible scenarios a correlated subquery can be a better option or the only option? (I use Oracle Database) Answer Oracle has a …

Teiid not performing optimal join

For our Teiid Springboot project we use a row filter in a where clause to determine what results a user gets. Example: SELECT * FROM very_large_table WHERE id IN (’01’, ’03’) We want the context in …

SQL query – List albums where every song matches criteria

I am analyzing my music database which uses SQLite. I am trying to get a list of all albums where all the songs on that album having a rating of 10 or less. Some contrived data for the example: Using this example data, I would want my query to return “Presence” since all the songs on that album ha…

How to count favorites and all data of an other table with JOIN

I try to select the favorites for each wines in my database and all data from wines but with the group by obligation, I can’t have data for wines which don’t have a favorite_score, so do you know how to do please. This is my SQL request for instance and now this my DB diagram : enter image descrip…

Find repeating phone numbers between a 7 day range

I have a phone and a call date field. I need to find all phone and call dates where calls were made more than once (>1) within a 7 day period. What is the best approach to this? Example: output: Here’s an example query I tried but I assume I can do better (besides, its taking a very long time,

Improving the performance of a MySQL left join sub query

I have the following MySQL query which calculates the total number of orders for each month within a given date range e.g. a year. The query works correctly, but the performance is slow (around 250ms). Any ideas on how to rewrite it to make it more efficient? UPDATE: Some have suggested using two left joins, …