5
It looks to me like it’s doing a Scan because it may well need rows from T553 if the condition in T1011 holds. On the other hand, if any of the conditions on T553 hold, it’ll need rows from T1011.
So indexes would have to be able to handle finding rows in T553 and then pulling in the relevant rows from T1011, and also finding rows in T1011 and pulling in the ...
2
We have an application generated query using a view that has two tables joined on a LEFT OUTER join. When filtering by fields from just one table (either table) an index seek happens and it's reasonably fast.
It is valid (= guaranteed to always produce correct results) to push a selection (aka filter, predicate) below an inner join when the selection ...
2
As an alternative to coalescing column values one may want to use a default row on the non matching rows
Using tsql dialect based on the comment: I've added a tag to indicate I would accept a Postgres specific answer. Still, standard SQL would be preferred if possible.
Test data
SELECT *
INTO #a
FROM (VALUES (1)
, (2)
, (3)) AS x(a1);
...
2
You can use only columns from the joined tables or in your case concatenated version of it
SELECT
first_name, last_name, address_1_zip,
CONCAT_WS(',', last_name,first_name) AS CombinedName
FROM
CPSQL.dbo.legal_entity AS LE
INNER JOIN CPSQL.dbo.cases as C ON CONCAT_WS(', ', LE.last_name,LE.first_name)=C.style
WHERE C.create_date >= Convert(...
1
Try to use explicit join. It seems that MySQL has some issue referencing the implicit cross join.
select b.id, u.id, rating
from books as b
join
users as u on 1=1
left join ratings on u.id = userid and b.id = bookid
1
Use set operations. If the user is 42, and the group is 1001, that would be
(SELECT user_id_a FROM userfriend
WHERE user_id_b = 42
UNION
SELECT user_id_b FROM userfriend
WHERE user_id_a = 42
)
EXCEPT
SELECT user_id FROM usergroup
WHERE group_id = 1001;
UNION creates a result set that is the union of two query results and removes duplicate rows. ...
1
Like most (all?) SQL queries the number of columns in the result is fixed. Recursive CTEs can only add rows and not columns.
You are fortunate that the result you want to generate a ARF report is a XML. Because of this you can use good old GROUP_CONCAT to generate an XML form of the DKIM.
I haven't looked up the XML spec for the report however something in ...
1
Move the text columns to a separate table. This new table will have the same key as the original table. When a new row arrives write the small columns to the original table and the text columns to the new table with the same key value. This technique is called a vertical partition. If required there can be multiple new tables, each containing one or more of ...
1
Maybe. That is, there is no setting to achieve such; however, rewriting the query can achieve such.
Please show us the query that is causing such. Meanwhile, here are some possible examples:
SELECT * ... loads all the text columns. If you don't need all the columns, spell out what you need instead of saying *.
SELECT big_text FROM t WHERE ... ORDER BY .....
1
A classical case of relational-division!
One of the most efficient queries techniques for what you ask:
SELECT h.name,
h.vacancies
FROM hotels_amenities_lookup ha1
JOIN hotels_amenities_lookup ha2 USING (hotel_id)
-- more?
JOIN hotels h ON h.id = ha1.hotel_id
WHERE ha1.amenity_id = (SELECT id FROM amenities WHERE key = 'wheelchair_accessible')...
Only top voted, non community-wiki answers of a minimum length are eligible
