Tag Info

New answers tagged

1

Since the alias column is nullable, if you add a unique constraint on the composite (last_name, first_name, birth_date, alias), there will still be duplicates allowed, with the same values in the first 3 columns and NULL in the alias. The constraint is skipped / accepted when at least one value is null. MySQL documentation on CREATE TABLE is not very clear ...


0

The timings could be bogus because of caching. You probably have the Query cache on. Try again, but with SELECT SQL_NO_CACHE ...; Recommend you add a new possibility to the ENUM behind this: `attempt_1_result` NOT IN ('complete,incorrect,decline,prospecting') OR `attempt_1_result` IS NULL That way you could avoid the NULL test and the OR. AND ...


1

Each variable-width column in a row will require a single bit to maintain the nullability status (this is so SQL Server can check this bit, rather than skipping down to the row and parsing the contents of that column). Of course you can't add a single bit to a row, you need to add a byte for this data, which means that if you have a single variable-width ...


1

When the supplier 'comes back' then do you want to set the EndDate to null again or do you want to keep track of the periods in which the supplier supplied to you? In the last case you must put the StartDate and EndDate in a separate table. If you put it in a separate table then you always must do a join to see if it is an active supplier unless you put an ...


1

CONCAT_WS('', -- hack, empty delimiter tadd.street_number, ' ', tadd.street_name,', ', CONCAT(tadd.apt_number,', '), -- hack, this line will become NULL, when apt_number is null, and will be omitted with delimiter tadd.city,', ', tadd.postal_code,', ', tadd.country) AS 'Address' I don't know mysql, ...


10

If you want to skip NULL values (but not empty strings), you can use CONCAT_WS() function: CONCAT_WS( ', ', -- Separator CONCAT_WS(' ', tadd.street_number, tadd.street_name), tadd.apt_number, tadd.city, tadd.postal_code, tadd.country ) AS Address From the docs: CONCAT_WS(separator,str1,str2,...) ...


4

Convert NULL values in empty string by wrapping it in COALESCE or IFNULL: IFNULL: SELECT CONCAT(IFNULL(tadd.street_number,''), ' ',IFNULL(tadd.street_name,''), ', ',IFNULL(tadd.apt_number,''), ', ',IFNULL(tadd.city,''), ', ',IFNULL(tadd.postal_code,''), ', ',IFNULL(tadd.country,'')) AS 'Address' FROM db.tbl; ...


3

CONCAT( tadd.street_number, ' ', tadd.street_name, ', ', -- concat() will return null if one is null, so ifnull returns empty string in that case IFNULL(CONCAT(tadd.apt_number, ', '), ''), tadd.city, ', ', tadd.postal_code, ', ',tadd.country ) AS 'Address'


4

This may not directly help you, since I only know SQL Server, but in any case this is how I would approach this: Sample Data: CREATE TABLE #Example ( Eid integer NULL ); INSERT #Example (Eid) VALUES (1), (2), (3), (4), (5), (NULL), (NULL), (NULL), (NULL), (NULL); My idea revolves around the idea that ...



Top 50 recent answers are included