Example: we have two tables tbl_students,tbl_marks using a join query to fetch the students not having
records in marks table.It should work using a single query
see the blow example without using subquery.
For creating students and marks table use the below query
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 |
CREATE TABLE IF NOT EXISTS `tbl_marks` ( `id` int(10) NOT NULL AUTO_INCREMENT, `studentid` int(10) NOT NULL, `marks` int(10) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=6 ; -- -- Dumping data for table `tbl_marks` -- INSERT INTO `tbl_marks` (`id`, `studentid`, `marks`) VALUES (1, 2, 10), (2, 3, 15), (3, 4, 11), (4, 5, 19), (5, 6, 20); -- -------------------------------------------------------- -- -- Table structure for table `tbl_students` -- CREATE TABLE IF NOT EXISTS `tbl_students` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(100) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=7 ; -- -- Dumping data for table `tbl_students` -- INSERT INTO `tbl_students` (`id`, `name`) VALUES (1, 'vasanthan'), (2, 'jayesh'), (3, 'nasar'), (4, 'sreejith'), (5, 'arun'), (6, 'clinton') |
use the below query to display null records
|
1 2 3 4 |
SELECT name, marks FROM tbl_students LEFT JOIN tbl_marks ON tbl_students.id = tbl_marks.studentid WHERE marks IS NULL |