Well, I believe it’s an “Old but Gold” question. The answer is: “It depends!”. The performances are such a delicate subject that it would be too much silly to say: “Never use subqueries, always join”. In the following links, you’ll find some basic best practices that I have found to be very helpful: Optimizing Subqueries … Read more
You can take the aggregations out into a CTE (common table expression): with minima as (select t.id, t.type, min(value) min_value from table2 t where t.type in (1,2,3,4) group by t.id, t.type) select a.id, a.name, (select min_value from minima where minima.id = subquery.id and minima.type = 1) as column1, (select min_value from minima where minima.id = … Read more
Apparently, I was too lazy to read to the end of the documentation… Further down, the documentation states: subquery A subquery is used only in conjunction with the XML keyword. When you specify a subquery, all values found by the subquery are used for pivoting. […] This will work PIVOT XML (AVG(salary) FOR (company) IN … Read more
Use: SELECT p.upc, p.name, ph.price, ph.date FROM PRODUCT p LEFT JOIN PRICE_H ph ON ph.product_id = p.id JOIN (SELECT a.product_id, MAX(a.date) AS max_date FROM PRICE_H a GROUP BY a.product_id) x ON x.product_id = ph.product_id AND x.max_date = ph.date
From this web article The reason for this error is that MySQL doesn’t allow updates to a table when you are also using that same table in an inner select as your update criteria. The article goes on to provide a solution, which is to use a temporary table. Using this example, your update should … Read more
select @code:= SUM(field1 + field2), @code+1 from abc; But, please be aware of the following (from the MySQL 5.6 docs): As a general rule, other than in SET statements, you should never assign a value to a user variable and read the value within the same statement. For example, to increment a variable, this is … Read more
Below is the pseudo-code for using sub-query using Criteria API. CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder(); CriteriaQuery<Object> criteriaQuery = criteriaBuilder.createQuery(); Root<EMPLOYEE> from = criteriaQuery.from(EMPLOYEE.class); Path<Object> path = from.get(“compare_field”); // field to map with sub-query from.fetch(“name”); from.fetch(“id”); CriteriaQuery<Object> select = criteriaQuery.select(from); Subquery<PROJECT> subquery = criteriaQuery.subquery(PROJECT.class); Root fromProject = subquery.from(PROJECT.class); subquery.select(fromProject.get(“requiredColumnName”)); // field to map with main-query subquery.where(criteriaBuilder.and(criteriaBuilder.equal(“name”,name_value),criteriaBuilder.equal(“id”,id_value))); select.where(criteriaBuilder.in(path).value(subquery)); … Read more
SELECT Country, COUNT(*) AS Total, COUNT(CASE WHEN Reconnect = true THEN 1 END) AS With_Reconnect FROM PlayerSession S LEFT JOIN Player P ON P.id = S.player_id GROUP BY country
I have encountered a similar issue in my project. The way I choose to implement it is a bit different for two reasons: If at one point in time Sequelize decides to implement sub queries – the syntax is ready. Use Sequelize protection against SQL injection. Here is my code snippet, hope it helps. Sequelize … Read more
If you want to avoid two subselects, the query can be rewritten like this: UPDATE table1 SET col1 = o_min, col2 = o_max FROM ( SELECT min(ship_charge) as o_min, max(ship_charge) as o_max FROM orders ) t WHERE col4 = 1001 If ship_charge is not indexed, this should be faster than two subselects. If ship_charge is … Read more