MySQL Basic Join and SubQuery

        

        //1. JOINS

        select t1.c1,t2.c2 from 
            table1 t1 join table2 t2
            on t1.c1 = t2.c1 ; 
    
        //multi table join
        select t1.c1,t2.c2 from 
            table1 t1 join table2 t2
                on t1.c1 = t2.c1 
                    join table3 t3 
                    on t2.c1 = t3.c1
                        join table4 t4 
                        on t3.c1 = t4.c1
                        where t3.date like '%_MAR_%';   // use of like
                    
    
    // SUBQUERY 
    
        1.SECOND HIGHEST DATA 
            select max(c1) from tn
                where c1 < (select max(c1) from tn);
    
            //with multi row
            select c1,c2 from tn where c1 =
                (select max(c1) from tn
                    where c1 < (select max(c1) from tn));
    
            //Third Highest (Swarnakar rule)
            select c1,c2 from tn where c1 = 
                (select max(c1) from tn  
                    where c1 <
                        (select max(c1) from tn
                            where c1 < (select max(c1) from tn)));