kovid-r

with dates 
as
  (
   select '2019-12-12' as _date union all
   select '2019/12/12' as _date union all
   select '2019.12.12' as _date union all
   select '2019_12_12' as _date union all
   select '2019\12\12' as _date union all
   select '2012#12#12' as _date union all
   select '2019$12$12' as _date union all

kovid-r

create database if not exists cast_ex;
create table if not exists cast_ex.table_1 (
  val_int int(10) not null,
  val_int_char varchar(10) not null,
  val_str varchar(10) not null
);

insert into cast_ex.table_1
with recursive seq 
  as (

kovid-r

select * from cast_ex.table_1 where val_int = 1;
select * from cast_ex.table_1 where val_int = '1';
select * from cast_ex.table_1 where val_int_char = 1;
select * from cast_ex.table_1 where val_int_char = '1';

kovid-r

alter table cast_ex.table_1 
  add index idx_val_int (val_int),
  add index idx_val_int_char(val_int_char);

explain select * from cast_ex.table_1 where val_int = '1';
explain select * from cast_ex.table_1 where val_int_char = 1;

explain select * from cast_ex.table_1 force index (idx_val_int_char) where val_int_char = 1;

kovid-r

SELECT t1.*, 
       (SELECT t2.field_name 
          FROM TABLE_2 AS t2
         WHERE t1.id = t2.id
         LIMIT 1
       ) 
  FROM TABLE_1 AS t1

kovid-r

SELECT city, 
  FROM TABLE_1 AS t1 
  LEFT JOIN TABLE_2 as t2
    ON t1.id = t2.id;

kovid-r

kovid-r

select null is null null_is_null, 
       null is not null null_is_not_null,
       1 = 0 1_0,
       0 is null 0_is_null,
       1 is null 1_is_null,
       0 = 1 is null 0_1_is_null,
       null = null null_equals_null,
       null <> null null_not_equals_null,
       null != null null_not_equals_null_2,
       null < null null_less_than_null,

kovid-r

/* Where evaluates the condition finally to a TRUE or FALSE */
SELECT 1 WHERE 1; -- returns one record
SELECT 1 WHERE 0; -- returns nothing
SELECT 1 WHERE 0.001; -- returns one record

/* Any non-zero number evaluates to TRUE and zero evaluates to FALSE */
SELECT 1 = TRUE; --  returns one record
SELECT 0 = FALSE; --  returns one record
SELECT 1 = FALSE; -- returns nothing

kovid-r

/* Let's look at how SQL handles NULLs */
SELECT 1 WHERE 1; -- returns one record
SELECT 1 WHERE NOT 1; -- returns nothing

/* You'd think that this applies everywhere, but it doesn't */
SELECT 1 WHERE NULL; -- returns nothing
SELECT 1 WHERE NOT NULL -- also returns nothing, what happened here?

/* What happened here? Check what these columns evaluate to */
SELECT 1, NOT 1; -- 1 evaluates to 1 and NOT 1 evaluates to 0